package com.amani.services;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.UUID;

import javax.jws.WebMethod;
import javax.jws.WebResult;
import javax.jws.WebService;

import net.sf.json.JsonConfig;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;

import com.amani.bean.Account;
import com.amani.bean.RepsonBean;
import com.amani.bean.SpadDconsumeInfo;
import com.amani.bean.SpadMconsumeInfo;
import com.amani.dao.AMN_Dao;
import com.amani.model.Cardaccount;
import com.amani.model.Cardaccountchangehistory;
import com.amani.model.Cardinfo;
import com.amani.model.CardinfoId;
import com.amani.model.CategoryinfoId;
import com.amani.model.Companyinfo;
import com.amani.model.Designer;
import com.amani.model.Dgoodsbarinfo;
import com.amani.model.Dnointernalcardinfo;
import com.amani.model.DnointernalcardinfoId;
import com.amani.model.Evaluation;
import com.amani.model.Goodsinfo;
import com.amani.model.Inventory;
import com.amani.model.InventoryId;
import com.amani.model.Inventorydetal;
import com.amani.model.InventorydetalId;
import com.amani.model.Nointernalcardinfo;
import com.amani.model.NointernalcardinfoId;
import com.amani.model.Orders;
import com.amani.model.OrdersEmpinfo;
import com.amani.model.OredersPrjinfo;
import com.amani.model.Project;
import com.amani.model.Projectinfo;
import com.amani.model.Roominfo;
import com.amani.model.StaffHairDesignerInfo;
import com.amani.model.Staffinfo;
import com.amani.model.VirtualCard;
import com.amani.model.Vouchers;
import com.amani.model.Vouchersdetal;
import com.amani.model.Weixinclent;
import com.amani.model.Works;
import com.amani.model.Wxbandcard;
import com.amani.spring.SpringDBInit;
import com.amani.tools.CommonTool;
import com.amani.tools.DESPlus;
import com.amani.tools.DataTool;
import com.amani.tools.HttpClientUtil;

@WebService(endpointInterface = "com.amani.services.ICommonService")
public class CommonService implements ICommonService {
	
	public static final Log LOG = LogFactory.getLog(ICommonService.class);
	private static final String String = null; 
	
	public String ClobToString(Clob clob) throws SQLException, IOException { 
		String reString = "";
		Reader is = clob.getCharacterStream();// 得到流
		BufferedReader br = new BufferedReader(is);
		String s = br.readLine();
		StringBuffer sb = new StringBuffer();
		while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
		sb.append(s);
		s = br.readLine();
		}
		reString = sb.toString();
		return reString;
	}
	public boolean updtaeCardCodeState(String cardCode,int state){
		Boolean result=false;
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="update [wx_card_code] set [state]="+state+" where [card_code]='"+cardCode+"'";
		try {
			result=amn_Dao.executeSql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally { 
		}
		return result;
	}
	
	public boolean updtaeOrderStateByCardCode(String cardCode,int orderState){
		Boolean result=false;
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="update [orders] set [orderstate]="+orderState+" where [card_code]='"+cardCode+"'";
		try {
			result=amn_Dao.executeSql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally { 
		}
		return result;
	}
	
	public boolean updtaeOrderCard(String orderno,String cardId,String cardCode){
		Boolean result=false;
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="update [orders] set [card_id]='"+cardId+"' ,[card_code]='"+cardCode+"' where [callbillid]='"+orderno+"'";
		try {
			result=amn_Dao.executeSql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally { 
		}
		return result;
	}
  

 
	public boolean updtaeOrderStateByOrderNo(String orderno,String OrderType,int orderState){
		Boolean result=false;
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="";
		if(OrderType.toLowerCase().equals("shop")){
			sql="update [wx_orders] set [orderstate]="+orderState+" where [orderno]='"+orderno+"'";
		}else{
			sql="update [orders] set [orderstate]="+orderState+" where [callbillid]='"+orderno+"'";
		}
		try {
			result=amn_Dao.executeSql(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally { 
		}
		return result;
	}
	
 
	public int getOrderStateByOrderNo(String orderno,String OrderType){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="";
		if(OrderType.toLowerCase().equals("shop")){
			sql="SELECT orderstate FROM [wx_orders] where orderno='"+orderno+"'";
		}else{
			sql="SELECT orderstate FROM [orders] where callbillid='"+orderno+"'";
		}
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("orderstate", rs.getObject("orderstate")==null?0: rs.getObject("orderstate")); 
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		if(reuslt.size()>0){
			return (int)reuslt.get(0).get("orderstate");
		}
		return -1; 
	}

 
	public String getCardcode(String cardId){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="SELECT card_code FROM wx_card_code WHERE   ([state] = 0) AND (card_id = '"+cardId+"')";
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("card_code", rs.getObject("card_code")==null?"": rs.getObject("card_code")); 
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		if(reuslt.size()>0){
			return (String)reuslt.get(0).get("card_code");
		}
		return ""; 
	}
	
	public List<Map<String, Object>> getNewsListByCid( String cid){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="SELECT top 10 [id] ,[uid]  ,[cid] ,[type]  ,[title]  ,[subtitle] ,[path] ,[descr] ,[content]  ,[source_from],[author],[link],[videopath],[hits],[tags],[isrec],[hot] ,[new],[praise] ,[fav] ,[isopen] ,[uptime],[createtime],[sort]"+
				  "FROM [dbo].[WX_amn_news]" +
						"where cid="+cid+" AND isopen=1";
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("id", rs.getObject("id"));
					tmp.put("uid", rs.getObject("uid")==null?0:rs.getObject("uid"));
					tmp.put("cid", rs.getObject("cid"));
					tmp.put("type", ((Integer)rs.getObject("type"))==0?false:true);
					tmp.put("title", rs.getObject("title"));
					tmp.put("subtitle", rs.getObject("subtitle"));
					tmp.put("path", rs.getObject("path"));
					tmp.put("descr", ClobToString((Clob)rs.getObject("descr")));
					tmp.put("content", ClobToString((Clob)rs.getObject("content")));
					tmp.put("source_from", rs.getObject("source_from")==null?"":rs.getObject("source_from"));
					tmp.put("author", rs.getObject("author")==null?"": rs.getObject("author"));
					tmp.put("link", rs.getObject("link"));
					tmp.put("videopath", rs.getObject("videopath")==null?"": rs.getObject("videopath"));
					tmp.put("hits", rs.getObject("hits"));
					tmp.put("tags", rs.getObject("tags")==null?"":rs.getObject("tags"));
					tmp.put("isrec",((Integer)rs.getObject("isrec"))==0?false:true);
					tmp.put("hot", ((Integer)rs.getObject("hot"))==0?false:true);
					tmp.put("new", ((Integer)rs.getObject("new"))==0?false:true);
					tmp.put("praise", rs.getObject("praise"));
					tmp.put("fav", rs.getObject("fav"));
					tmp.put("isopen",((Integer) rs.getObject("isopen"))==0?false:true);
					tmp.put("uptime", rs.getObject("uptime")==null?0:rs.getObject("uptime"));
					tmp.put("createtime", rs.getObject("createtime"));
					tmp.put("sort", rs.getObject("sort")==null?0:rs.getObject("sort"));
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return reuslt; 
	}
 
	public Map<String, Object> getNewsById( String id){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="SELECT top 10 [id] ,[uid]  ,[cid] ,[type]  ,[title]  ,[subtitle] ,[path] ,[descr] ,[content]  ,[source_from],[author],[link],[videopath],[hits],[tags],[isrec],[hot] ,[new],[praise] ,[fav] ,[isopen] ,[uptime],[createtime],[sort]"+
				  "FROM [dbo].[WX_amn_news]" +
						"where id="+id;
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("id", rs.getObject("id"));
					tmp.put("uid", rs.getObject("uid")==null?0:rs.getObject("uid"));
					tmp.put("cid", rs.getObject("cid"));
					tmp.put("type", ((Integer)rs.getObject("type"))==0?false:true);
					tmp.put("title", rs.getObject("title"));
					tmp.put("subtitle", rs.getObject("subtitle"));
					tmp.put("path", rs.getObject("path"));
					tmp.put("descr", ClobToString((Clob)rs.getObject("descr")));
					tmp.put("content", ClobToString((Clob)rs.getObject("content")));
					tmp.put("source_from", rs.getObject("source_from")==null?"":rs.getObject("source_from"));
					tmp.put("author", rs.getObject("author")==null?"": rs.getObject("author"));
					tmp.put("link", rs.getObject("link"));
					tmp.put("videopath", rs.getObject("videopath")==null?"": rs.getObject("videopath"));
					tmp.put("hits", rs.getObject("hits"));
					tmp.put("tags", rs.getObject("tags")==null?"":rs.getObject("tags"));
					tmp.put("isrec",((Integer)rs.getObject("isrec"))==0?false:true);
					tmp.put("hot", ((Integer)rs.getObject("hot"))==0?false:true);
					tmp.put("new", ((Integer)rs.getObject("new"))==0?false:true);
					tmp.put("praise", rs.getObject("praise"));
					tmp.put("fav", rs.getObject("fav"));
					tmp.put("isopen",((Integer) rs.getObject("isopen"))==0?false:true);
					tmp.put("uptime", rs.getObject("uptime")==null?0:rs.getObject("uptime"));
					tmp.put("createtime", rs.getObject("createtime"));
					tmp.put("sort", rs.getObject("sort")==null?0:rs.getObject("sort"));
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		if(reuslt.size()>0){
			return reuslt.get(0);
		}
		return null; 
	}
	 
	
	public int saveWxOrderStep1(boolean isExpress,String orderno,double price,String openid,String memberno, String address,String username,String phone,String compno,String compname,String companyaddress){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="";
		int id=-1;
		if(isExpress){ 
			address=""; 
			username=""; 
			phone="";
			sql="insert wx_orders(orderno,totalAmount,useropenid,memberno,ordertime,orderstate,catgory,isExpress,address,username,phone,compno,compname,companyaddress)"+
						  "values('"+getString(orderno)+"',"+price+",'"+getString(openid)+"','"+getString(memberno)+"',getdate(),0,2,1,'"+getString(address)+"','"+getString(username)+"','"+getString(phone)+"','"+getString(compno)+"','"+getString(compname)+"','"+getString(companyaddress)+"')";
		}else{
			compno="";
		    compname=""; 
			companyaddress="";
			sql="insert wx_orders(orderno,totalAmount,useropenid,memberno,ordertime,orderstate,catgory,isExpress,address,username,phone,compno,compname,companyaddress)"+
					  "values('"+getString(orderno)+"',"+price+",'"+getString(openid)+"','"+getString(memberno)+"',getdate(),0,2,0,'"+getString(address)+"','"+getString(username)+"','"+getString(phone)+"','"+getString(compno)+"','"+getString(compname)+"','"+getString(companyaddress)+"')";

		}
		ResultSet rs = null;
		try {
			amn_Dao.executeSql(sql);
			sql="Select id from wx_orders where orderno='"+orderno+"'";
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					id=rs.getInt("id");
					break;
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return id;
	}
	public Map<String, Object> loadWxOrderByOrderNo(String orderno){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="SELECT [id],[orderno],[totalAmount] ,[useropenid],[catgory] ,[memberno] ,[ordertime] ,[orderstate] ,[isExpress] ,[address] ,[username] ,[phone] ,[compno] ,[compname] ,[companyaddress] ,[remark]"+
				  " FROM [TestAMN].[dbo].[wx_orders]"+ 
				  " WHERE [orderno]='"+orderno+"'";
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("id", rs.getObject("id"));
					tmp.put("orderno", rs.getObject("orderno"));
					tmp.put("totalAmount", rs.getObject("totalAmount"));
					tmp.put("useropenid", rs.getObject("useropenid"));
					tmp.put("catgory", rs.getObject("catgory"));
					tmp.put("memberno", rs.getObject("memberno")==null?"": rs.getObject("memberno"));
					tmp.put("ordertime", rs.getObject("ordertime"));
					tmp.put("orderstate", rs.getObject("orderstate"));
					tmp.put("isExpress", rs.getObject("isExpress"));
					tmp.put("address", rs.getObject("address")==null?"": rs.getObject("address"));
					tmp.put("username", rs.getObject("username")==null?"": rs.getObject("username"));
					tmp.put("phone", rs.getObject("phone")==null?"": rs.getObject("phone"));
					tmp.put("compno", rs.getObject("compno")==null?"": rs.getObject("compno"));
					tmp.put("compname", rs.getObject("compname")==null?"": rs.getObject("compname"));
					tmp.put("companyaddress", rs.getObject("companyaddress")==null?"": rs.getObject("companyaddress"));
					tmp.put("remark", rs.getObject("remark")==null?"": rs.getObject("remark"));
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		if(reuslt.size()>0){
			return reuslt.get(0);
		}
		return null;
	}
 
	public void saveWxOrderStep2(int parentId, int produectId,int number){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="insert wx_order_detail(parentid,amn_product_id,number)"+
					"values("+parentId+","+produectId+","+number+")"; 
		try {
			amn_Dao.executeSql(sql); 
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}

	 
	public List<Map<String, Object>> loadProductDescription(String produectId){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="  SELECT imageDescription, imageurl FROM wx_product_detail WHERE parentId= '"+produectId+"'" ;
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("imageDescription", rs.getObject("imageDescription"));
					tmp.put("imageurl", rs.getObject("imageurl"));
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return reuslt; 
	}
	
	
	public List<Map<String, Object>> loadProductInfo(String produectId){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql=" SELECT   wx_product.id, wx_product_group.name as groupname,wx_product.type,"+
					 "wx_product.name , wx_product.cid, wx_product.price, wx_product.originalcost,  "+
					 "isnull(wx_product.card_id,'') as card_id, isnull(wx_product.content,'') as content, wx_product.ishot, wx_product.intro, wx_product.mainimageurl, "+
					  "isnull(wx_product.sort,0) as sort, wx_product.sumnum, wx_product.isopen,isnull(wx_product.minute,0) as minute "+
					 "FROM      wx_product INNER JOIN "+
					 "wx_product_group ON wx_product.[group] = wx_product_group.id  "+
					 "where isopen=1 and catgory=2  and  wx_product.id='"+produectId+"'" ;
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("id", rs.getObject("id"));
					tmp.put("groupname", rs.getObject("groupname"));
					tmp.put("name", rs.getObject("name"));
					tmp.put("type", rs.getObject("type"));
					tmp.put("cid", rs.getObject("cid"));
					tmp.put("price", rs.getObject("price"));
					tmp.put("originalcost", rs.getObject("originalcost"));
					tmp.put("cardId", rs.getObject("card_id"));
					tmp.put("content", rs.getObject("content"));
					tmp.put("ishot", rs.getObject("ishot")); 
					tmp.put("intro", rs.getObject("intro"));
					tmp.put("mainimageurl", rs.getObject("mainimageurl"));
					tmp.put("sort", rs.getObject("sort"));
					tmp.put("sumnum", rs.getObject("sumnum"));
					tmp.put("isopen", rs.getObject("isopen"));
					tmp.put("minute", rs.getObject("minute"));
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return reuslt; 
	}


	 
	public List<Map<String, Object>> loadProductsByGroupId(String goupId){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql=" SELECT   wx_product.id, wx_product_group.name as groupname,wx_product.type,"+
					 "wx_product.name , wx_product.cid, wx_product.price, wx_product.originalcost,  "+
					 "isnull(wx_product.card_id,'') as card_id ,isnull( wx_product.content,'') as content, wx_product.ishot, wx_product.intro, wx_product.mainimageurl, "+
					  "isnull(wx_product.sort,0) as sort, wx_product.sumnum, wx_product.isopen,isnull(wx_product.minute,0) as minute "+
					 "FROM      wx_product INNER JOIN "+
					 "wx_product_group ON wx_product.[group] = wx_product_group.id  "+
					 "where isopen=1 and catgory=2  and wx_product_group.id='"+goupId+"'" ;
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("id", rs.getObject("id"));
					tmp.put("groupname", rs.getObject("groupname"));
					tmp.put("name", rs.getObject("name"));
					tmp.put("type", rs.getObject("type"));
					tmp.put("cid", rs.getObject("cid"));
					tmp.put("price", rs.getObject("price"));
					tmp.put("originalcost", rs.getObject("originalcost"));
					tmp.put("cardId", rs.getObject("card_id"));
					tmp.put("content", rs.getObject("content"));
					tmp.put("ishot", rs.getObject("ishot")); 
					tmp.put("intro", rs.getObject("intro"));
					tmp.put("mainimageurl", rs.getObject("mainimageurl"));
					tmp.put("sort", rs.getObject("sort"));
					tmp.put("sumnum", rs.getObject("sumnum"));
					tmp.put("isopen", rs.getObject("isopen"));
					tmp.put("minute", rs.getObject("minute"));
					reuslt.add(tmp);
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return reuslt; 
	}

	
	public List<Map<String, Object>> loadProductGroup(){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql="  SELECT   name, id    FROM      wx_product_group   " ;
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("id", rs.getObject("id"));
					tmp.put("name", rs.getObject("name"));
					reuslt.add(tmp);
				}
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return reuslt; 
	}

	public List<Map<String, Object>> loadHotProducts() {
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> lsCompanyinfos = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String sql=" SELECT   wx_product.id, wx_product_group.name as groupname,wx_product.type, "+
                "wx_product.name , wx_product.cid, wx_product.price, wx_product.originalcost, "+
                "wx_product.card_id, wx_product.content, wx_product.ishot, wx_product.intro, wx_product.mainimageurl, "+
                " isNull(wx_product.sort,0) as sort , wx_product.sumnum, wx_product.isopen,isnull(wx_product.minute,0) as minute "+ 
				"FROM wx_product INNER JOIN "+ 
                "wx_product_group ON wx_product.[group] = wx_product_group.id "+   
				" where isopen=1 and catgory=1  and ishot=1 ";
		ResultSet rs = null;
		
		try {
			rs = amn_Dao.executeQuery(sql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("id", rs.getObject("id"));
					tmp.put("groupname", rs.getObject("groupname"));
					tmp.put("type", rs.getObject("type"));
					tmp.put("name", rs.getObject("name"));
					tmp.put("cid", rs.getObject("cid"));
					tmp.put("price", rs.getObject("price"));
					tmp.put("originalcost", rs.getObject("originalcost")); 
					tmp.put("cardId", rs.getObject("card_id"));
					tmp.put("content", rs.getObject("content"));
					tmp.put("ishot", rs.getObject("ishot"));
					tmp.put("intro", rs.getObject("intro"));
					tmp.put("mainimageurl", rs.getObject("mainimageurl"));
					tmp.put("sort", rs.getObject("sort"));
					tmp.put("sumnum", rs.getObject("sumnum"));
					tmp.put("isopen", rs.getObject("isopen"));
					tmp.put("minute", rs.getObject("minute"));
					lsCompanyinfos.add(tmp);
				}
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return lsCompanyinfos;
	}

	// 获取指定openid预约过的设计师
	public List<Map<String, Object>>  loadConsumedStaffinfos(String openid,String projecttype) { 
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao"); 
		String strSql ="";
		if(projecttype.equals("001")){
			 strSql="select distinct staffinfo.staffno,staffinfo.staffopenid, isnull(staffinfo.imageurl,'')  AS staffimageurl, staffinfo.staffname, "+
	             "isnull(staffinfo.description,'') as description,staffinfo.compno,companyinfo.compipaddress,companyinfo.compname, "+
	             "companyinfo.xcoordinate,companyinfo.ycoordinate, "+
	             "5 as starnum, "+
	             "(case staffinfo.position when '003' then '美发师' when '006' then '首席'  when '007' then '总监' when '00701' then '创意总监'  when '00702' then '店长' else ''  end) as position,"+ 
			     "wx_card.price,wx_card.card_id "+
			     "from orders inner join Staffinfo on orders.confirmemp = staffinfo.staffno "+
			     "inner join companyinfo on Staffinfo.compno = companyinfo.compno "+
			     "left join wx_card on wx_card.compid=staffinfo.compno and "+
			     "((staffinfo.position ='003' and wx_card.paramid='SP075') "+
			     "or (staffinfo.position ='006' and wx_card.paramid='SP076') "+
			     "or (staffinfo.position ='007' and wx_card.paramid='SP077') "+
			     "or (staffinfo.position ='00701' and wx_card.paramid='SP078') "+
			     "or (staffinfo.position ='00702' and wx_card.paramid='SP079') ) "+
			     "where   orders.confirmdate  between DATEADD (month,-3,getdate()) and getdate() "+
			     "and staffinfo.position in('003','006','007','00701','00702') "+
				 "and substring(staffinfo.staffno,4,6)<>'300' "+
				 "and  orders .openid='"+openid+"'";
		}else {
			strSql="select distinct staffinfo.staffno,staffinfo.staffopenid, isnull(staffinfo.imageurl,'')  AS staffimageurl, staffinfo.staffname, "+
				   "staffinfo.description,staffinfo.compno,companyinfo.compipaddress,companyinfo.compname, "+
				   "companyinfo.xcoordinate,companyinfo.ycoordinate, "+
					"5 as starnum,  0 as price,  '' as card_id "+
					"from orders inner join Staffinfo on orders.confirmemp = staffinfo.staffno "+ 
					"inner join companyinfo on Staffinfo.compno = companyinfo.compno "+
					"where   orders.confirmdate  between DATEADD (month,-3,getdate()) and getdate() "+ 
					"and staffinfo.position in('004','00401','00402','00103') "+
					"and substring(staffinfo.staffno,4,6)<>'400' "+
					"and  orders .openid='"+openid+"'";
		} 
		ResultSet rs = null;
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try { 
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("staffno", rs.getObject("staffno"));
					tmp.put("staffopenid", rs.getObject("staffopenid"));
					tmp.put("staffimageurl", rs.getObject("staffimageurl"));
					tmp.put("staffname", rs.getObject("staffname"));
					tmp.put("description", rs.getObject("description"));
					tmp.put("compno", rs.getObject("compno"));
					tmp.put("compipaddress", rs.getObject("compipaddress"));
					tmp.put("compname", rs.getObject("compname"));
					tmp.put("xcoordinate", rs.getObject("xcoordinate"));
					tmp.put("ycoordinate", rs.getObject("ycoordinate"));
					tmp.put("starnum", rs.getObject("starnum"));
					if(projecttype.equals("001")){
						tmp.put("position", rs.getObject("position"));
					}
					tmp.put("price", rs.getObject("price")==null?0:rs.getObject("price"));
					tmp.put("cardId", rs.getObject("card_id")==null?"":rs.getObject("card_id"));
					result.add(tmp);
				}
			} 
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	public List<Map<String, Object>>  loadStaffinfosByCompno(String compno,String projecttype,int n){
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao"); 
		String strSql ="";
		if(projecttype.equals("001")){
			 strSql="select top "+n+" staffinfo.staffno,staffinfo.staffopenid, isnull(staffinfo.imageurl,'') AS staffimageurl, staffinfo.staffname, "+
					"isnull(staffinfo.description,'') as description,staffinfo.compno,companyinfo.compipaddress,companyinfo.compname, "+
					"companyinfo.xcoordinate,companyinfo.ycoordinate, "+
					"5 as starnum, Staffinfo.[mobilephone],companyinfo.[compaddress],"+
					"(case staffinfo.position when '003' then '美发师' when '006' then '首席' when '007' then '总监' when '00701' then '创意总监' when '00702' then '店长' else ''  end) as position, "+ 
					"isnull(wx_card.price,0) as price,isnull(wx_card.card_id ,'') as card_id "+
					"from  Staffinfo  inner join companyinfo on Staffinfo.compno = companyinfo.compno "+
					"left join wx_card on wx_card.compid=staffinfo.compno and "+
					"((staffinfo.position ='003' and wx_card.paramid='SP075') "+
					"or (staffinfo.position ='006' and wx_card.paramid='SP076') "+
					"or (staffinfo.position ='007' and wx_card.paramid='SP077') "+
					"or (staffinfo.position ='00701' and wx_card.paramid='SP078') "+
					"or (staffinfo.position ='00702' and wx_card.paramid='SP079') ) "+
					"where staffinfo.position in('003','006','007','00701','00702') "+
					"and substring(staffinfo.staffno,4,6)<>'300' "+
					"and companyinfo.compno = '"+compno+"'";
		}else {
			strSql="select top "+n+" staffinfo.staffno,staffinfo.staffopenid, isnull(staffinfo.imageurl,'')  AS staffimageurl, staffinfo.staffname, "+
					"isnull(staffinfo.description,'') as description,staffinfo.compno,companyinfo.compipaddress,companyinfo.compname, "+
					"companyinfo.xcoordinate,companyinfo.ycoordinate, "+
					"5 as starnum, '美容' as position, Staffinfo.[mobilephone],companyinfo.[compaddress],"+
					"0 as price,  '' as card_id "+
					"from Staffinfo  inner join companyinfo on Staffinfo.compno = companyinfo.compno "+
					"where staffinfo.position in('004','00401','00402','00103') "+
					"and substring(staffinfo.staffno,4,6)<>'400' "+
					"and companyinfo.compno = '"+compno+"'";
		} 
		ResultSet rs = null;
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try { 
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("staffno", rs.getObject("staffno"));
					tmp.put("staffopenid", rs.getObject("staffopenid"));
					tmp.put("staffimageurl", rs.getObject("staffimageurl"));
					tmp.put("staffname", rs.getObject("staffname"));
					tmp.put("description", rs.getObject("description"));
					tmp.put("compno", rs.getObject("compno"));
					tmp.put("compipaddress", rs.getObject("compipaddress"));
					tmp.put("compname", rs.getObject("compname"));
					tmp.put("xcoordinate", rs.getObject("xcoordinate"));
					tmp.put("ycoordinate", rs.getObject("ycoordinate"));
					tmp.put("starnum", rs.getObject("starnum")); 
					tmp.put("position", rs.getObject("position")); 
					tmp.put("price", rs.getObject("price"));
					tmp.put("card_id", rs.getObject("card_id"));
					tmp.put("mobilephone", rs.getObject("mobilephone"));
					tmp.put("compaddress", rs.getObject("compaddress"));
					result.add(tmp);
				}
			} 
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}

	/**
	 * 根据openid获取会员卡信息
	 */ 
	public List<Map<String, Object>> loadMemberinfoByOpenid(String openid)
	{
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao"); 
		String strSql ="select top 1 memberno as cardno , membername,membermphone from memberinfo where openid ='"+openid+"'";
		ResultSet rs = null;
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try { 
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Map<String, Object> tmp = new HashMap<String, Object>();
					tmp.put("cardno", rs.getObject("cardno"));
					tmp.put("membername", rs.getObject("membername"));
					tmp.put("membermphone", rs.getObject("membermphone"));
					result.add(tmp);
				}
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	/**
	 * 获取指定openid附近门店
	 * 
	 * @param lon1
	 *            第一点的精度
	 * @param lat1
	 *            第一点的纬度
	 * @return 所有员工
	 * */
	List<Map.Entry<Companyinfo, Double>> loadNearbyCompanyinfos(double lon,
			double lat) {
		List<Companyinfo> companyInfos = this.loadAllCompanyinfo();
		Hashtable<Companyinfo, Double> map = new Hashtable<Companyinfo, Double>();
		for (int i = 0; i < companyInfos.size(); i++) {
			double x = Double.parseDouble(companyInfos.get(i).getXcoordinate());
			double y = Double.parseDouble(companyInfos.get(i).getYcoordinate());
			// 获取距离
			double distance = getDistance(lon, lat, x, y);

			map.put(companyInfos.get(i), distance);

		}
		// 这里将map.entrySet()转换成list
		List<Map.Entry<Companyinfo, Double>> list = new ArrayList<Map.Entry<Companyinfo, Double>>(
				map.entrySet());
		// 然后通过比较器来实现排序
		Collections.sort(list,
				new Comparator<Map.Entry<Companyinfo, Double>>() {
					// 升序排序
					public int compare(Entry<Companyinfo, Double> o1,
							Entry<Companyinfo, Double> o2) {
						return o1.getValue().compareTo(o2.getValue());
					}

				});

		return list;

	}

	// 获取指定openid附近门店的设计师
	/**
	 * 获取指定openid附近门店的设计师
	 * 
	 * @param lon1
	 *            第一点的精度
	 * @param lat1
	 *            第一点的纬度
	 * @return 所有员工
	 * */
	public List<Staffinfo> loadNearbyStaffinfos(double lon, double lat,
			int projecttype) {
		List<Map.Entry<Companyinfo, Double>> companysEntries = loadNearbyCompanyinfos(
				lon, lat);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		List<Staffinfo> resultStaffinfos = new ArrayList<Staffinfo>();
		 
		for (Entry<Companyinfo, Double> entry : companysEntries) {
			//每次最多返回15条纪律
			 if (resultStaffinfos.size()>=15) {
				break;
			}
			String strCompId = entry.getKey().getCompno();
			List<Staffinfo> lsStaffinfos = new ArrayList<Staffinfo>();
			try {
				if ("1".equals(projecttype)) {
					lsStaffinfos = amn_Dao
							.findByHql(" From Staffinfo where isnull(businessflag,0)=1 and position in('003','006','007','00701','00702') and compno='"
									+ strCompId
									+ "' "
									+ " and isnull(curstate,'')='2' and substring(staffno,4,6)<>'003' ");
				} else {
					lsStaffinfos = amn_Dao
							.findByHql(" From Staffinfo where isnull(businessflag,0)=1 and position in('004','00401','00402','00103') and compno='"
									+ strCompId
									+ "' "
									+ " and isnull(curstate,'')='2' and substring(staffno,4,6)<>'004' ");
				}

				if (lsStaffinfos != null && lsStaffinfos.size() > 0) {
					for (int i = 0; i < lsStaffinfos.size()&& i<5; i++) {
						lsStaffinfos
								.get(i)
								.getId()
								.setStaffno(
										lsStaffinfos.get(i).getId()
												.getStaffno().substring(3));
						lsStaffinfos.get(i).setCommpanyId(
								entry.getKey().getCompno());
						lsStaffinfos.get(i).setCommpanyName(
								entry.getKey().getCompname());
						lsStaffinfos.get(i).setDistance(entry.getValue());
					}
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
			resultStaffinfos.addAll(lsStaffinfos);
		}
		return resultStaffinfos;
	}

	private static final double EARTH_RADIUS = 6378137;// 赤道半径(单位m)

	/**
	 * 转化为弧度(rad)
	 * */
	private static double rad(double d) {
		return d * Math.PI / 180.0;
	}

	/**
	 * 基于googleMap中的算法得到两经纬度之间的距离,计算精度与谷歌地图的距离精度差不多,相差范围在0.2米以下
	 * 
	 * @param lon1
	 *            第一点的精度
	 * @param lat1
	 *            第一点的纬度
	 * @param lon2
	 *            第二点的精度
	 * @param lat3
	 *            第二点的纬度
	 * @return 返回的距离,单位km
	 * */
	public static double getDistance(double lon1, double lat1, double lon2,
			double lat2) {
		double radLat1 = rad(lat1);
		double radLat2 = rad(lat2);
		double a = radLat1 - radLat2;
		double b = rad(lon1) - rad(lon2);
		double s = 2 * Math.asin(Math.sqrt(Math.pow(Math.sin(a / 2), 2)
				+ Math.cos(radLat1) * Math.cos(radLat2)
				* Math.pow(Math.sin(b / 2), 2)));
		s = s * EARTH_RADIUS;
		s = Math.round(s * 10000) / 10000;
		return s;
	}

	public Companyinfo loadCompanyinfo(String strCompId) {
		AMN_Dao amn_Dao = null;
		Companyinfo companyinfo = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			List<Companyinfo> lsCompanyinfos = amn_Dao
					.findByHql(" From Companyinfo where compstate<>3 and compno='"
							+ strCompId + "' ");
			if (lsCompanyinfos != null && lsCompanyinfos.size() > 0) {
				companyinfo = lsCompanyinfos.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return companyinfo;

	}

	public List<Companyinfo> loadAllCompanyinfo() {
		AMN_Dao amn_Dao = null;
		List<Companyinfo> lsCompanyinfos = new ArrayList<Companyinfo>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			lsCompanyinfos = amn_Dao
					.findByHql("select c From Companyinfo c,Compchainstruct d where curcompno=compno and complevel=4 and compstate<>3");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lsCompanyinfos;
	}

	public List<Projectinfo> loadProjectinfo(String strCompId) {
		DataTool dataTool = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		dataTool = (DataTool) sdbinit.getInstance().getBean("dataTool");
		List<Projectinfo> lsProjectinfos = dataTool
				.loadProjectinfoByCompId(strCompId);
		return lsProjectinfos;
	}

	public List<Goodsinfo> loadAllGoodinfo() {
		AMN_Dao amn_Dao = null;
		List<Goodsinfo> lsgGoodsinfos = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			lsgGoodsinfos = amn_Dao
					.findByHql(" From Goodsinfo where isnull(goodsusetype,0) in(1,3) and goodswarehouse in('01','02')");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lsgGoodsinfos;
	}

	public String loadStaffinfo(String strCompId, String strType) {
		AMN_Dao amn_Dao = null;
		List<Staffinfo> lsStaffinfos = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			if ("1".equals(strType)) {
				lsStaffinfos = amn_Dao
						.findByHql(" From Staffinfo where isnull(businessflag,0)=1 and position in('003','006','007','00701','00702') and compno='"
								+ strCompId
								+ "' "
								+ " and isnull(curstate,'')='2' and substring(staffno,4,6)<>'003' ");
			} else {
				lsStaffinfos = amn_Dao
						.findByHql(" From Staffinfo where isnull(businessflag,0)=1 and position in('004','00401','00402','00103') and compno='"
								+ strCompId
								+ "' "
								+ " and isnull(curstate,'')='2' and substring(staffno,4,6)<>'004' ");
			}

			if (lsStaffinfos != null && lsStaffinfos.size() > 0) {
				for (int i = 0; i < lsStaffinfos.size(); i++) {
					lsStaffinfos
							.get(i)
							.getId()
							.setStaffno(
									lsStaffinfos.get(i).getId().getStaffno()
											.substring(3));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsStaffinfos);
		return jsonarray.toString();
	}

	public List<CategoryinfoId> loadCategory(String strCompId) {
		AMN_Dao amn_Dao = null;
		List<CategoryinfoId> lsCategoryinfos = new ArrayList<CategoryinfoId>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		try {
			rs = amn_Dao
					.executeQuery(" select b.*,categoryname from categoryinfo a,categoryinfoid b where a.compno=b.compno and a.categoryno=b.categoryno and a.compno='"
							+ strCompId + "'");
			if (rs != null) {
				while (rs.next()) {
					CategoryinfoId categoryinfoId = new CategoryinfoId();
					categoryinfoId.setCategoryno(rs.getString("categoryno"));
					categoryinfoId.setCompno(rs.getString("compno"));
					categoryinfoId
							.setCategoryname(rs.getString("categoryname"));
					categoryinfoId.setPostationid(rs.getString("postationid"));
					lsCategoryinfos.add(categoryinfoId);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return lsCategoryinfos;
	}

	public List<Roominfo> loadRoomInfo(String strCompId) {
		AMN_Dao amn_Dao = null;
		List<Roominfo> lsRoominfos = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			lsRoominfos = amn_Dao.findByHql(" From Roominfo where compno='"
					+ strCompId + "'");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lsRoominfos;
	}

	public List<String> loadMirror(String strCompId) {
		Companyinfo companyinfo = loadCompanyinfo(strCompId);
		List<String> lsStrings = new ArrayList<String>();
		if (companyinfo == null) {
			return null;
		} else {
			if (companyinfo.getMirrornumber() != null) {
				if (CommonTool.FormatInteger(companyinfo.getMirrornumber()
						.intValue()) > 0) {
					for (int i = 0; i < companyinfo.getMirrornumber()
							.intValue(); i++) {
						lsStrings.add((i + 1) + "号镜台");
					}
					return lsStrings;
				}
			}
		}
		return null;
	}

	public List<Cardinfo> loadCardInfos(String strPhone, String strCardNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select top 25 a.cardvesting,a.cardno,a.cardtype, b.membername,b.membermphone "
				+ " from  cardinfo a left join memberinfo b on  a.cardno=b.memberno"
				+ " where   "
				+ "   (cardno='"
				+ strCardNo
				+ "' or '"
				+ strCardNo
				+ "' ='' ) "
				+
				// " and  (membername like '%"+searchMemberNameKey+"%' or '"+searchMemberNameKey+"' ='' ) "+
				" and  (membermphone='"
				+ strPhone
				+ "' or '"
				+ strPhone
				+ "' ='' ) " +
				// " and  (memberpaperworkno='"+searchMemberPCIDKey+"' or '"+searchMemberPCIDKey+"' ='' )"
				// +
				" group by a.cardvesting,a.cardno,a.cardtype, b.membername,b.membermphone   ";
		List<Cardinfo> lsCardinfos = new ArrayList<Cardinfo>();
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				Cardinfo record = null;
				while (rs.next()) {
					record = new Cardinfo();
					record.setBcardvesting(CommonTool.FormatString(rs
							.getString("cardvesting")));
					record.setBcardno(CommonTool.FormatString(rs
							.getString("cardno")));
					record.setCardtype(CommonTool.FormatString(rs
							.getString("cardtype")));
					record.setMembername(CommonTool.FormatString(rs
							.getString("membername")));
					record.setMemberphone(CommonTool.FormatString(rs
							.getString("membermphone")));
					lsCardinfos.add(record);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return lsCardinfos;
	}

	public Cardinfo loadCardInfo(String strCompId, String strCardNo) {
		AMN_Dao amn_Dao = null;
		Cardinfo returnValue = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strModeId = this.loadSysParam(strCompId, "SP063");
		String strFristModeId = "";// 第一级模板Id
		String strSecondModeId = "";// 第2级模板ID
		String strThirthModeId = "";// 第三级模板Id
		// 先定位模板门店的连锁级别(暂时支持4级连锁)
		int compLvl = this.loadCompLvl(strCompId);
		if (compLvl == 2) {
			strFristModeId = this.loadparentModeId(strModeId);
		} else if (compLvl == 3) {
			strSecondModeId = this.loadparentModeId(strModeId);
			if (!strSecondModeId.equals(""))
				strFristModeId = this.loadparentModeId(strSecondModeId);
		} else if (compLvl == 4) {
			strThirthModeId = this.loadparentModeId(strModeId);
			if (!strThirthModeId.equals(""))
				strSecondModeId = this.loadparentModeId(strThirthModeId);
			if (!strSecondModeId.equals(""))
				strFristModeId = this.loadparentModeId(strSecondModeId);
		}
		String strSql = "select a.cardvesting,a.cardno,a.cardstate,a.salecarddate,a.cutoffdate,a.cardsource,membersex,a.cardtype,cardtypename,membername,membermphone,memberpaperworkno,lowopenamt,lowfillamt,openflag,fillflag,changeflag,sendamtflag,slaeproerate,changerule, "
				+ " accountbalance2=sum(case when isnull(accounttype,0)=2 then accountbalance else 0 end ),accountdebts2=sum(case when isnull(accounttype,0)=2 then accountdebts else 0 end ), "
				+ " accountbalance3=sum(case when isnull(accounttype,0)=3 then accountbalance else 0 end ),accountdebts3=sum(case when isnull(accounttype,0)=3 then accountdebts else 0 end ), "
				+ " accountbalance4=sum(case when isnull(accounttype,0)=4 then accountbalance else 0 end ),accountdebts4=sum(case when isnull(accounttype,0)=4 then accountdebts else 0 end ), "
				+ " accountbalance5=sum(case when isnull(accounttype,0)=5 then accountbalance else 0 end ),accountdebts5=sum(case when isnull(accounttype,0)=5 then accountdebts else 0 end ), "
				+ " accountbalance6=sum(case when isnull(accounttype,0)=6 then accountbalance else 0 end ),accountdebts6=sum(case when isnull(accounttype,0)=6 then accountdebts else 0 end ) "
				+ " from cardaccount c with(nolock),cardtypeinfo d with(nolock), cardinfo a with(nolock) left join memberinfo b with(nolock) on  a.cardno=b.memberno  "
				+ " where a.cardno=c.cardno and a.cardno='"
				+ strCardNo
				+ "'"
				+ "   and cardstate in(4,5) "
				+ " and a.cardtype=d.cardtypeno and  cardtypemodeid  in ('"
				+ strModeId
				+ "','"
				+ strFristModeId
				+ "','"
				+ strSecondModeId
				+ "','"
				+ strThirthModeId
				+ "') "
				+ " group by   a.cardvesting,a.cardno,a.cardstate,a.salecarddate,a.cutoffdate,a.cardsource,membersex,a.cardtype,cardtypename,membername,membermphone,memberpaperworkno,lowopenamt,lowfillamt,openflag,fillflag,changeflag,sendamtflag,slaeproerate,changerule ";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					returnValue = new Cardinfo();
					returnValue.setId(new CardinfoId(rs
							.getString("cardvesting"), rs.getString("cardno")));
					returnValue.setCardstate(CommonTool.FormatInteger(rs
							.getInt("cardstate")));
					returnValue.setSalecarddate(CommonTool.getDateMask(rs
							.getString("salecarddate")));
					returnValue.setCutoffdate(CommonTool.getDateMask(rs
							.getString("cutoffdate")));
					returnValue.setCardtype(CommonTool.FormatString(rs
							.getString("cardtype")));
					returnValue.setCardsource(CommonTool.FormatInteger(rs
							.getInt("cardsource")));
					returnValue.setCardtypeName(CommonTool.FormatString(rs
							.getString("cardtypename")));
					returnValue.setMembername(CommonTool.FormatString(rs
							.getString("membername")));
					returnValue.setMembersex(CommonTool.FormatInteger(rs
							.getInt("membersex")));
					returnValue.setMemberphone(CommonTool.FormatString(rs
							.getString("membermphone")));
					returnValue.setMemberpaperworkno(CommonTool.FormatString(rs
							.getString("memberpaperworkno")));
					returnValue.setAccount2Amt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountbalance2"))));
					returnValue.setAccount3Amt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountbalance3"))));
					returnValue.setAccount4Amt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountbalance4"))));
					returnValue.setAccount5Amt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountbalance5"))));
					returnValue.setAccount6Amt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountbalance6"))));
					returnValue.setAccount2debtAmt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountdebts2"))));
					returnValue.setAccount3debtAmt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountdebts3"))));
					returnValue.setAccount4debtAmt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountdebts4"))));
					returnValue.setAccount5debtAmt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountdebts5"))));
					returnValue.setAccount6debtAmt(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("accountdebts6"))));
					returnValue.setSlaeproerate(CommonTool
							.FormatBigDecimal(new BigDecimal(rs
									.getDouble("slaeproerate"))));

					returnValue
							.setDSaleLowAmt(CommonTool
									.FormatBigDecimal(new BigDecimal(CommonTool
											.FormatDouble(rs
													.getDouble("lowopenamt")))));

					returnValue
							.setDFillLowAmt(CommonTool
									.FormatBigDecimal(new BigDecimal(CommonTool
											.FormatDouble(rs
													.getDouble("lowfillamt")))));
					returnValue.setOpenflag(CommonTool.FormatInteger(rs
							.getInt("openflag")));
					returnValue.setFillflag(CommonTool.FormatInteger(rs
							.getInt("fillflag")));
					returnValue.setChangeflag(CommonTool.FormatInteger(rs
							.getInt("changeflag")));
					returnValue.setSendamtflag(CommonTool.FormatInteger(rs
							.getInt("sendamtflag")));
					returnValue.setChangerule(CommonTool.FormatInteger(rs
							.getInt("changerule")));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		double saleamt = this.loadSaleCardNoPromotions(strCompId, strCardNo);
		if (CommonTool.FormatDouble(saleamt) != 0) {
			returnValue.setDSaleLowAmt(new BigDecimal(saleamt));
		}
		saleamt = this.loadFillCardNoPromotions(strCompId, strCardNo);
		if (CommonTool.FormatDouble(saleamt) != 0) {
			returnValue.setDFillLowAmt(new BigDecimal(saleamt));
		}
		return returnValue;
	}

	public String loadSysParam(String strCompId, String strValue) {

		String strField = "";
		String strSql = "";
		String strReturnValue = "";
		if (strValue == null || strValue.equals("")) {
			return strReturnValue;
		} else {
			AMN_Dao amn_Dao = null;
			SpringDBInit sdbinit = SpringDBInit.getInstance();
			amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
			strSql = "select paramid,paramvalue from sysparaminfo where compid = '"
					+ strCompId
					+ "' and paramid = "
					+ CommonTool.quotedStr(strValue);
			ResultSet rs = null;
			try {
				rs = amn_Dao.executeQuery(strSql);
				if (rs != null) {
					while (rs.next()) {
						strReturnValue = CommonTool.FormatString(rs
								.getString("paramvalue"));
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		return strReturnValue;
	}

	public String loadparentModeId(String strCurModeId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "  select parentmodeid from syscommoninfomode where  isnull(modeid,'')='"
				+ strCurModeId + "' ";
		ResultSet rs = null;
		String parentmodeid = "";
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					parentmodeid = CommonTool.FormatString(rs
							.getString("parentmodeid"));
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return parentmodeid;
	}

	public int loadCompLvl(String strCompId) {

		String strSql = " select complevel from compchainstruct where curcompno='"
				+ strCompId + "' ";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		int returnValue = 0;
		try {
			if (rs != null) {
				while (rs.next()) {
					returnValue = CommonTool.FormatInteger(rs
							.getInt("complevel"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return returnValue;
	}

	public double loadSaleCardNoPromotions(String strCompId, String strCardNo) {
		String strSql = " select promotionsvalue from promotionsinfo where promotionsstore='"
				+ strCompId
				+ "' and promotionstype=4 and promotionscode='"
				+ strCardNo
				+ "' and startdate<='"
				+ CommonTool.getCurrDate()
				+ "'  and enddate>='"
				+ CommonTool.getCurrDate()
				+ "' and isnull(invalid,0)=0 ";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		double returnValue = 0.0;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					returnValue = CommonTool.FormatDouble(rs
							.getDouble("promotionsvalue"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return returnValue;
	}

	public double loadFillCardNoPromotions(String strCompId, String strCardNo) {
		String strSql = " select promotionsvalue from promotionsinfo where promotionsstore='"
				+ strCompId
				+ "' and promotionstype=5 and promotionscode='"
				+ strCardNo
				+ "' and startdate<='"
				+ CommonTool.getCurrDate()
				+ "'  and enddate>='"
				+ CommonTool.getCurrDate()
				+ "' and isnull(invalid,0)=0 ";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		double returnValue = 0.0;

		try {
			if (rs != null && rs.next() == true) {
				returnValue = CommonTool.FormatDouble(rs
						.getDouble("promotionsvalue"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return returnValue;
	}

	public boolean saveIPADBillInfo(SpadMconsumeInfo spadMconsumeInfo,
			List<SpadDconsumeInfo> spadDconsumeInfo) {
		try {
			AMN_Dao amn_Dao = null;
			SpringDBInit sdbinit = SpringDBInit.getInstance();
			amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
			amn_Dao.save(spadDconsumeInfo);
			amn_Dao.saveOrUpdateAll(spadDconsumeInfo);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

	}

	public String loadVouchers() {
		String strSql = " From Vouchers ";
		ArrayList<Vouchers> lsVouchers = new ArrayList<Vouchers>();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			lsVouchers = (ArrayList<Vouchers>) amn_Dao.findAll(Vouchers.class);
			if (lsVouchers != null && lsVouchers.size() > 0) {
				for (Vouchers vouchers : lsVouchers) {
					if (vouchers.getVchtype() == 1) {
						vouchers.setVchname("项目抵用券");
					}
					if (vouchers.getVchtype() == 2) {
						vouchers.setVchname("现金抵用券");
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsVouchers);
		return jsonarray.toString();
	}

	public Vouchers loadVoucher(String strVchNo) {
		String strSql = " From Vouchers where vchno='" + strVchNo + "' ";
		AMN_Dao amn_Dao = null;
		Vouchers voucher = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			List<Vouchers> lsVouchers = amn_Dao.findByHql(strSql);
			if (lsVouchers != null && lsVouchers.size() > 0) {
				voucher = lsVouchers.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return voucher;
	}

	public List<Vouchersdetal> loadVouchersdetals() {
		String strSql = " From Vouchersdetal ";
		List<Vouchersdetal> lsVouchersdetals = new ArrayList<Vouchersdetal>();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			lsVouchersdetals = amn_Dao.findByHql(strSql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lsVouchersdetals;
	}

	public List<Vouchersdetal> loadVouchersdetalsById(String strVchNo) {
		String strSql = " From Vouchersdetal where vchno='" + strVchNo + "' ";
		List<Vouchersdetal> lsVouchersdetals = new ArrayList<Vouchersdetal>();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			lsVouchersdetals = amn_Dao.findByHql(strSql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lsVouchersdetals;
	}

	public boolean checkDyqNo(String strDyqNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select 1 from nointernalcardinfo where cardno='"
				+ strDyqNo + "'";
		try {
			if (amn_Dao.getRowsCount_Ex(strSql) > 0) {
				return false;
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public Nointernalcardinfo loadNointernalcardinfo(String strDyqNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " From Nointernalcardinfo where cardno='" + strDyqNo
				+ "' ";
		Nointernalcardinfo nointernalcardinfo = null;
		try {
			List<Nointernalcardinfo> lsNointernalcardinfos = amn_Dao
					.findByHql(strSql);
			if (lsNointernalcardinfos != null
					&& lsNointernalcardinfos.size() > 0) {
				nointernalcardinfo = lsNointernalcardinfos.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nointernalcardinfo;
	}

	public String loadDqyNo(String strOpenId, String strClientName,
			String strMobile, String strVchNo, String strBillId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		Vouchers vouchers = loadVoucher(strVchNo);
		if (vouchers == null) {
			return "false";
		}
		String dyqNo = "RP" + CommonTool.randomString(12).toUpperCase();
		while (checkDyqNo(dyqNo) == false) {
			dyqNo = "RP" + CommonTool.randomString(12);
		}
		List<Dnointernalcardinfo> lsDnointernalcardinfos = new ArrayList<Dnointernalcardinfo>();
		Weixinclent weixinclent = new Weixinclent();
		weixinclent.setClientname(strClientName);
		weixinclent.setCreatedate(CommonTool.getCurrDate());
		weixinclent.setMobile(strMobile);
		weixinclent.setOperid(strOpenId);
		weixinclent.setQdyno(dyqNo);
		weixinclent.setVchno(strVchNo);
		weixinclent.setBillid(strBillId);

		Nointernalcardinfo nointernalcardinfo = new Nointernalcardinfo();
		NointernalcardinfoId id = new NointernalcardinfoId();
		id.setCardvesting("001");
		id.setCardno(dyqNo);
		id.setCreatecardtype("");
		id.setCreatecardtypename("");
		nointernalcardinfo.setId(id);
		nointernalcardinfo
				.setCardfaceamt(BigDecimal.valueOf(vouchers.getAmt()));
		nointernalcardinfo.setCarduseflag(vouchers.getVchtype());
		nointernalcardinfo.setEntrytype(0);
		nointernalcardinfo.setCardstate(1);
		// nointernalcardinfo.setUsedate(CommonTool.getCurrDate());
		nointernalcardinfo.setOldvalidate(CommonTool.getCurrDate());
		nointernalcardinfo.setEnabledate(CommonTool.getCurrDate());
		nointernalcardinfo.setLastvalidate(CommonTool.addMonth(
				CommonTool.getCurrDate(), 6));
		nointernalcardinfo.setBillid(strBillId);
		nointernalcardinfo.setPhone(strMobile);
		nointernalcardinfo.setCardtype(1);
		if (vouchers.getVchtype() == 1) {
			List<Vouchersdetal> lsVouchersdetals = loadVouchersdetalsById(strVchNo);
			if (lsVouchersdetals != null && lsVouchersdetals.size() > 0) {
				for (int i = 0; i < lsVouchersdetals.size(); i++) {
					Dnointernalcardinfo dnointernalcardinfo = new Dnointernalcardinfo();
					DnointernalcardinfoId dnoId = new DnointernalcardinfoId();
					dnoId.setCardvesting("001");
					dnoId.setCardno(dyqNo);
					dnoId.setSeqno((double) i);
					dnointernalcardinfo.setId(dnoId);
					dnointernalcardinfo.setIneritemno(lsVouchersdetals.get(i)
							.getId().getItemno());
					dnointernalcardinfo.setEntrycount(BigDecimal.valueOf(1));
					dnointernalcardinfo.setUsecount(BigDecimal.valueOf(0));
					dnointernalcardinfo.setLastcount(BigDecimal.valueOf(1));
					dnointernalcardinfo.setEntryamt(BigDecimal.valueOf(vouchers
							.getAmt()));
					dnointernalcardinfo.setUseamt(BigDecimal.valueOf(0));
					dnointernalcardinfo.setLastamt(BigDecimal.valueOf(vouchers
							.getAmt()));
					lsDnointernalcardinfos.add(dnointernalcardinfo);
				}
			}
		}
		try {
			amn_Dao.save(nointernalcardinfo);
			amn_Dao.save(weixinclent);
			amn_Dao.saveOrUpdateAll(lsDnointernalcardinfos);
			return dyqNo;
		} catch (Exception e) {
			e.printStackTrace();
			return "false";
		}
	}

	public String loadCardAccount(String strCardNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strsql = "From Cardaccount where cardno='" + strCardNo + "' ";
		List<Cardaccount> lsCardaccounts = null;
		try {
			lsCardaccounts = amn_Dao.findByHql(strsql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsCardaccounts);
		return jsonarray.toString();
		// return lsCardaccounts;
	}

	public String loadCardproaccounts(String strCompId, String strCardNo) {
		/*
		 * AMN_Dao amn_Dao=null; SpringDBInit sdbinit =
		 * SpringDBInit.getInstance();
		 * amn_Dao=(AMN_Dao)sdbinit.getInstance().getBean("amn_Dao"); String
		 * strsql="From Cardproaccount where cardno='"+strCardNo+"' ";
		 * List<Cardproaccount> lsCardproaccounts=null; try {
		 * lsCardproaccounts=amn_Dao.findByHql(strsql); } catch (Exception e) {
		 * e.printStackTrace(); } return lsCardproaccounts;
		 */
		try {
			DataTool dataTool = null;
			SpringDBInit sdbinit = SpringDBInit.getInstance();
			dataTool = (DataTool) sdbinit.getInstance().getBean("dataTool");
			strCompId = dataTool.LoadCardVestingByCardNo(strCardNo);

			net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
					.fromObject(dataTool.loadProInfosByCardNo(strCompId,
							strCardNo));
			return jsonarray.toString();
			// return dataTool.loadProInfosByCardNo(strCompId,strCardNo);

		} catch (Exception ex) {
			ex.printStackTrace();
			return "";
		}
	}

	public String loadcCardaccountchangehistories(String strCardNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strsql = "select top 3 * From Cardaccountchangehistory left join commoninfo a on(infotype='ZHLX' and changeaccounttype=parentcodekey) where changecardno='"
				+ strCardNo
				+ "' and changeaccounttype<>9 and changebilltype<>'NK' order by chagedate desc";
		List<Cardaccountchangehistory> lsCardaccountchangehistories = new ArrayList<Cardaccountchangehistory>();
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strsql);
			if (rs != null) {
				while (rs.next()) {
					Cardaccountchangehistory cardaccountchangehistory = new Cardaccountchangehistory();
					cardaccountchangehistory.setStrAccountTypeName(rs
							.getString("parentcodevalue"));
					if ("SY".equalsIgnoreCase(rs.getString("changebilltype"))) {
						cardaccountchangehistory.setChangebilltype("消费");
					} else if ("CZ".equalsIgnoreCase(rs.getString("changebilltype"))) {
						cardaccountchangehistory.setChangebilltype("充值");
					} else if ("SK".equalsIgnoreCase(rs.getString("changebilltype"))) {
						cardaccountchangehistory.setChangebilltype("售卡");
					}
					cardaccountchangehistory.setChagedate(CommonTool.getDateMask(rs.getString("chagedate")));
					cardaccountchangehistory.setChangeamt(rs.getDouble("changeamt"));
					lsCardaccountchangehistories.add(cardaccountchangehistory);
				}
			}
			// lsCardaccountchangehistories=amn_Dao.findByPage(3, 0,strsql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsCardaccountchangehistories);
		return jsonarray.toString();
		// return lsCardaccountchangehistories;
	}

	public Wxbandcard loadWxbandcard(String strCardNo, String operId) {
		Wxbandcard wxbandcard = loadWxBandCards(strCardNo);
		if (wxbandcard == null) {
			if (saveWxBandCard(strCardNo, operId)) {
				wxbandcard = loadWxBandCards(strCardNo);
			}
		}
		return wxbandcard;
	}

	public boolean saveWxBandCard(String strCardNo, String openid) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		Wxbandcard wxbandcard = new Wxbandcard();
		wxbandcard.setUuid(UUID.randomUUID().toString());
		wxbandcard.setCardno(strCardNo);
		wxbandcard.setCreatedate(CommonTool.getNowTime());
		wxbandcard.setRandomno(CommonTool.randomNumeric(18));
		wxbandcard.setOpenid(openid);
		wxbandcard.setValidate(CommonTool.addSeconds(
				wxbandcard.getCreatedate(), 60));
		try {
			amn_Dao.save(wxbandcard);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public Wxbandcard loadWxBandCards(String strCardNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " From Wxbandcard where cardno='" + strCardNo
				+ "' and validate>='" + CommonTool.getNowTime() + "' ";
		Wxbandcard wxbandcard = null;
		try {
			List<Wxbandcard> lsWxbandcards = amn_Dao.findByHql(strSql);
			if (lsWxbandcards != null && lsWxbandcards.size() > 0) {
				wxbandcard = lsWxbandcards.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wxbandcard;
	}

	public boolean updateCardPassword(String strCardNo, String strPassword) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			DESPlus desPlus = new DESPlus();
			Cardinfo cardinfo = null;
			List<Cardinfo> lsCardinfos = amn_Dao
					.findByHql("From Cardinfo where cardno='" + strCardNo
							+ "' ");
			if (lsCardinfos != null && lsCardinfos.size() > 0) {
				cardinfo = lsCardinfos.get(0);
				cardinfo.setCostpassword(desPlus.encrypt(strPassword));
				amn_Dao.saveOrUpdate(cardinfo);
			}
			return true;
			// String
			// strSql="update cardinfo set costpassword='"+desPlus.encrypt(strPassword)+"' where cardno='"+strCardNo+"' ";
			// return amn_Dao.executeQuery(strSql);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean checkCardPassword(String strCardNo, String strPassword) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			DESPlus desPlus = new DESPlus();
			String strSql = " select 1 from cardinfo where cardno='"
					+ strCardNo + "' and costpassword='"
					+ desPlus.encrypt(strPassword) + "' ";
			if (amn_Dao.getRowsCount_Ex(strSql) > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public String lsDnointernalcardinfos(String strDyqNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strsql = "From Dnointernalcardinfo where cardno='" + strDyqNo
				+ "' ";
		List<Dnointernalcardinfo> lsDnointernalcardinfos = null;
		try {
			lsDnointernalcardinfos = amn_Dao.findByHql(strsql);
		} catch (Exception e) {
			e.printStackTrace();
		}

		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsDnointernalcardinfos);
		return jsonarray.toString();
		// return lsDnointernalcardinfos;
	}

	public String loadwWeixinclents(String strOperId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select * from Weixinclent a with(nolock),nointernalcardinfo with(nolock),Vouchers b with(nolock)  where a.vchno=b.vchno and qdyno=cardno and operid='"
				+ strOperId + "'";
		ResultSet rs = null;
		List<Weixinclent> lsWeixinclents = new ArrayList<Weixinclent>();
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Weixinclent weixinclent = new Weixinclent();
					weixinclent.setBillid(rs.getString("billid"));
					weixinclent.setClientname(rs.getString("clientname"));
					weixinclent.setMobile(rs.getString("mobile"));
					weixinclent.setOperid(rs.getString("operid"));
					weixinclent.setCreatedate(rs.getString("enabledate"));
					weixinclent.setDyqName(rs.getString("vchname"));
					weixinclent.setQdyno(rs.getString("qdyno"));
					if (rs.getInt("cardstate") == 1) {
						weixinclent.setStrState("正常");
					} else if (rs.getInt("cardstate") == 2) {
						weixinclent.setStrState("已使用");
					}
					lsWeixinclents.add(weixinclent);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsWeixinclents);
		return jsonarray.toString();
		// return lsWeixinclents;
	}

	public boolean wxPost(String strCompId, String strBillId, String strDate) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " exec upg_handconsumbill_card '" + strCompId + "','"
				+ strBillId + "','" + strDate + "',2 ";
		Map<String, String> mapParam = new HashMap<String, String>();
		mapParam.put("orderid", strBillId);
		try {
			String strRep = HttpClientUtil.postMap(
					"http://amani.chinamani.com/api/sendmsg", mapParam);
			// System.out.println(strRep);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return amn_Dao.executeSql(strSql);
	}

	public boolean saveEvaluation(String billid, String operid,
			double discount, String content, double amt) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		Evaluation evaluation = new Evaluation();
		try {
			evaluation.setUuid(UUID.randomUUID().toString());
			evaluation.setBillid(billid);
			evaluation.setOperid(operid);
			evaluation.setDiscount(discount);
			evaluation.setContent(content);
			evaluation.setAmt(amt);
			amn_Dao.save(evaluation);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public Evaluation loadEvaluation(String operid, String billid) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		Evaluation evaluation = null;
		try {
			List<Evaluation> lsEvaluations = amn_Dao
					.findByHql(" From Evaluation where billid='" + billid
							+ "' and operid='" + operid + "'");
			if (lsEvaluations != null && lsEvaluations.size() > 0) {
				evaluation = lsEvaluations.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return evaluation;
	}

	public boolean saveOrders(String strCompId, String strBillId,
			String strCardNo, String orderPhone, String strDate,
			String strTime, String openid, int billType, String complydetail) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			Orders orders = new Orders();
			orders.setCallbillid(strBillId);
			orders.setOrderconply(strCompId);
			orders.setOrderphone(orderPhone);
			orders.setCardno(strCardNo);
			orders.setOrderfactdate(strDate);
			orders.setOrderfacttime(strTime);
			orders.setConfirmdate(CommonTool.getCurrDate());
			orders.setOpenid(openid);
			orders.setOrderstate(2);
			orders.setComplydetail(complydetail);
			orders.setBilltype(billType);
			orders.setOrdersid(UUID.randomUUID().toString());
			amn_Dao.saveOrUpdate(orders);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public String loadOrders(String openid) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		List<Orders> lsOrders = new ArrayList<Orders>();
		String strSql = " select orderfactdate,orderfacttime,orderstate,compaddress,complydetail,cardno,callbillid,orderconply,compname from orders with(nolock),companyinfo with(nolock) where compno=orderconply and openid='"
				+ openid
				+ "' and confirmdate between '"
				+ CommonTool.addMonth(CommonTool.getCurrDate(), -3)
				+ "' and '"
				+ CommonTool.getCurrDate() + "' ";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			while (rs.next()) {
				Orders orders = new Orders();
				orders.setCallbillid(rs.getString("callbillid"));
				orders.setOrderconply(rs.getString("orderconply"));
				orders.setOrderfactdate(rs.getString("orderfactdate"));
				orders.setComplydetail(rs.getString("complydetail"));
				orders.setOrderfacttime(rs.getString("orderfacttime"));
				orders.setStrCompName(rs.getString("compname"));
				orders.setAddress(rs.getString("compaddress"));
				orders.setOrderstate(rs.getInt("orderstate"));
				List<OrdersEmpinfo> lsEmpinfos = loadOrdersEmpinfo(
						rs.getString("orderconply"), rs.getString("callbillid"));
				if (lsEmpinfos != null && lsEmpinfos.size() > 0) {
					for (OrdersEmpinfo orEmpinfo : lsEmpinfos) {
						if (orders.getConfirmemp() == null) {
							orders.setConfirmemp(orEmpinfo.getEmpname());
						} else {
							orders.setConfirmemp(orders.getConfirmemp() + ","
									+ orEmpinfo.getEmpname());
						}
					}
				}

				List<OredersPrjinfo> lsOredersPrjinfos = loadOredersPrjinfo(
						rs.getString("orderconply"), rs.getString("callbillid"));
				if (lsOredersPrjinfos != null && lsOredersPrjinfos.size() > 0) {
					for (OredersPrjinfo orPrjinfo : lsOredersPrjinfos) {
						if (orders.getOrderproject() == null) {
							orders.setOrderproject(orPrjinfo.getPrjname());
						} else {
							orders.setOrderproject(orders.getOrderproject()
									+ "," + orPrjinfo.getPrjname());
						}
					}
				}
				lsOrders.add(orders);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsOrders);
		return jsonarray.toString();
	}

	public String loadCompByCardNo(String strCardNo) {
		List<Companyinfo> lsCompanyinfos = null;
		lsCompanyinfos = loadCompanysByCard(strCardNo);
		if (lsCompanyinfos == null || lsCompanyinfos.size() < 1) {
			lsCompanyinfos = new ArrayList<Companyinfo>();
			lsCompanyinfos.add(loadCompanyByCardNo(strCardNo));
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsCompanyinfos);
		return jsonarray.toString();
	}

	public List<Companyinfo> loadCompanysByCard(String strCardNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append(" select changecompid,compname ");
		sqlBuffer.append(" from( select top 3 changecompid ");
		sqlBuffer.append(" from cardaccountchangehistory  ");
		sqlBuffer.append(" where chagedate between '"
				+ CommonTool.addMonth(CommonTool.getCurrDate(), -3) + "' and '"
				+ CommonTool.getCurrDate() + "' ");
		sqlBuffer.append(" and changecardno='" + strCardNo + "' ");
		sqlBuffer.append(" group by changecompid) a,companyinfo b ");
		sqlBuffer.append(" where changecompid=compno ");
		List<Companyinfo> lsCompanyinfos = new ArrayList<Companyinfo>();
		try {
			rs = amn_Dao.executeQuery(sqlBuffer.toString());
			if (rs != null) {
				while (rs.next()) {
					Companyinfo companyinfo = new Companyinfo();
					companyinfo.setCompno(rs.getString("changecompid"));
					companyinfo.setCompname(rs.getString("compname"));
					lsCompanyinfos.add(companyinfo);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return lsCompanyinfos;
	}

	public Companyinfo loadCompanyByCardNo(String strCardNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		StringBuffer sqlbuBuffer = new StringBuffer();
		sqlbuBuffer.append(" select cardvesting,compname  ");
		sqlbuBuffer.append(" from cardinfo,companyinfo  ");
		sqlbuBuffer.append(" where cardvesting=compno ");
		sqlbuBuffer.append(" and cardno='" + strCardNo + "' ");
		Companyinfo companyinfo = new Companyinfo();
		try {
			rs = amn_Dao.executeQuery(sqlbuBuffer.toString());
			if (rs != null) {
				while (rs.next()) {
					companyinfo.setCompno(rs.getString("changecompid"));
					companyinfo.setCompname(rs.getString("compname"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return companyinfo;
	}

	public String loadEmpInfoByComp(String strCompId, String strCardNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strBeforDate = CommonTool.datePlusDay(CommonTool.getCurrDate(),
				-92);
		String strSql = "select substring(staffno,4,3) staffno,staffname,manageno ,ccount=COUNT(distinct b.csbillid)"
				+ " from staffinfo a with(nolock),mconsumeinfo b with(nolock),dconsumeinfo c with(nolock)"
				+ " where b.cscompid=c.cscompid and b.csbillid=c.csbillid"
				+ " and b.financedate between '"
				+ strBeforDate
				+ "' and '"
				+ CommonTool.getCurrDate()
				+ "' "
				+ " and b.cscardno='"
				+ strCardNo
				+ "' "
				+ " and ((c.csfirstinid=a.manageno and csfirsttype=1 ) "
				+ " or (c.cssecondinid=a.manageno and cssecondtype=1 )"
				+ " or (c.csthirdinid=a.manageno and csthirdtype=1)) "
				+ " and position in ('003','004','00401','00402','006','007','00701','00702','00103') and  a.compno='"
				+ strCompId + "' " + " group by staffno,staffname,manageno ";

		/*
		 * strSql+=
		 * " union all  select staffno,staffname,manageno ,ccount=COUNT(distinct b.csbillid)"
		 * +
		 * " from staffinfo a with(nolock),mconsumeinfo b with(nolock),dconsumeinfo c with(nolock)"
		 * + " where b.cscompid=c.cscompid and b.csbillid=c.csbillid" +
		 * " and b.financedate between '"
		 * +strBeforDate+"' and '"+CommonTool.getCurrDate()+"' " +
		 * " and b.cscardno='"+strCardNo+"' " +
		 * " and ((c.csfirstinid=a.manageno and csfirsttype=1) " +
		 * " or (c.cssecondinid=a.manageno and cssecondtype=1)" +
		 * " or (c.csthirdinid=a.manageno and csthirdtype=1)) " +
		 * " and position in ('003','004','00401','00402','006','007','00701','00702','00103') "
		 * + " group by staffno,staffname,manageno ";
		 */
		ResultSet rs = null;
		List<Staffinfo> lsStaffinfos = new ArrayList<Staffinfo>();
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Staffinfo staffinfo = new Staffinfo();
					staffinfo.setBstaffno(rs.getString("staffno"));
					staffinfo.setStaffename(rs.getString("staffname"));
					staffinfo.setManageno(rs.getString("manageno"));
					lsStaffinfos.add(staffinfo);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsStaffinfos);
		return jsonarray.toString();
	}

	public String loadEmpInfoByDept(String strCompId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " From Staffinfo where compno='" + strCompId
				+ "' and department in('004','003') ";
		List<Staffinfo> lsStaffinfos = null;
		try {
			lsStaffinfos = amn_Dao.findByHql(strSql);
			if (lsStaffinfos != null && lsStaffinfos.size() > 0) {
				for (int i = 0; i < lsStaffinfos.size(); i++) {
					lsStaffinfos
							.get(i)
							.getId()
							.setStaffno(
									lsStaffinfos.get(i).getId().getStaffno()
											.substring(3));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsStaffinfos);
		return jsonarray.toString();
	}

	public boolean saveOrderPrjInfo(String strCompId, String strBillId,
			String strPrjInfo, String strPrjName) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		OredersPrjinfo orPrjinfo = new OredersPrjinfo();
		orPrjinfo.setBillid(strBillId);
		orPrjinfo.setCompid(strCompId);
		orPrjinfo.setPrjno(strPrjInfo);
		orPrjinfo.setPrjname(strPrjName);
		orPrjinfo.setUuid(UUID.randomUUID().toString());
		try {
			amn_Dao.saveOrUpdate(orPrjinfo);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean saveOrderEmpInfo(String strCompId, String strBillId,
			String strEmpNo, String strEmpName, String strEmpInid) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		OrdersEmpinfo ordersEmp = new OrdersEmpinfo();
		ordersEmp.setBillid(strBillId);
		ordersEmp.setCompid(strCompId);
		ordersEmp.setEmpno(strEmpNo);
		ordersEmp.setEmpname(strEmpName);
		ordersEmp.setEmpinid(strEmpInid);
		ordersEmp.setUuid(UUID.randomUUID().toString());
		try {
			amn_Dao.saveOrUpdate(ordersEmp);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<OrdersEmpinfo> loadOrdersEmpinfo(String strCompId,
			String strBillId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select * from orders_empinfo where compid='"
				+ strCompId + "' and billid='" + strBillId + "' ";
		ResultSet rs = null;
		List<OrdersEmpinfo> lsEmpinfos = new ArrayList<OrdersEmpinfo>();
		try {
			rs = amn_Dao.executeQuery(strSql);
			while (rs.next()) {
				OrdersEmpinfo orEmpinfo = new OrdersEmpinfo();
				orEmpinfo.setBillid(rs.getString("billid"));
				orEmpinfo.setCompid(rs.getString("compid"));
				orEmpinfo.setEmpinid(rs.getString("empinid"));
				orEmpinfo.setEmpno(rs.getString("empno"));
				orEmpinfo.setEmpname(rs.getString("empname"));
				orEmpinfo.setUuid(rs.getString("uuid"));
				lsEmpinfos.add(orEmpinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return lsEmpinfos;
	}

	public List<OredersPrjinfo> loadOredersPrjinfo(String strCompId,
			String strBillId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select * from oreders_prjinfo where compid='"
				+ strCompId + "' and billid='" + strBillId + "' ";
		ResultSet rs = null;
		List<OredersPrjinfo> lsOredersPrjinfos = new ArrayList<OredersPrjinfo>();
		try {
			rs = amn_Dao.executeQuery(strSql);
			while (rs.next()) {
				OredersPrjinfo oredersPrjinfo = new OredersPrjinfo();
				oredersPrjinfo.setBillid(rs.getString("billid"));
				oredersPrjinfo.setCompid(rs.getString("compid"));
				oredersPrjinfo.setPrjno(rs.getString("prjno"));
				oredersPrjinfo.setPrjname(rs.getString("prjname"));
				oredersPrjinfo.setUuid(rs.getString("uuid"));
				lsOredersPrjinfos.add(oredersPrjinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return lsOredersPrjinfos;
	}

	public boolean updBillState(String strCompId, String strBillId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			return amn_Dao
					.executeSql(" update billstate set state=2 where compid='"
							+ strCompId + "' and billid='" + strBillId + "' ");
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public String checkBillState(String strBillId) {
		String state = "";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select state from billstate where billid='"
				+ strBillId + "'";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs.next()) {
				state = rs.getString("state");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return state;
	}

	public boolean updOrderState(String strBillId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			return amn_Dao
					.executeSql(" update orders set orderstate=3  where callbillid='"
							+ strBillId + "' ");
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	// 格式 YGBD#身份证号码#工号#openid#
	public boolean bandStaffInfo(String strParams) {
		if (CommonTool.checkStr(strParams)) {
			String arrayParams[] = strParams.split("#");
			if (arrayParams == null || arrayParams.length < 4) {
				return false;
			} else {
				AMN_Dao amn_Dao = null;
				SpringDBInit sdbinit = SpringDBInit.getInstance();
				amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
				ResultSet rs = null;
				try {

					if (amn_Dao
							.getRowsCount_Ex(" select count(staffno) from staffinfo where staffno='"
									+ arrayParams[2]
									+ "' and pccid='"
									+ arrayParams[1] + "' ") > 0) {
						return amn_Dao
								.executeSql(" update staffinfo set staffopenid='"
										+ arrayParams[3]
										+ "' where staffno='"
										+ arrayParams[2]
										+ "' and pccid='"
										+ arrayParams[1] + "' ");
					}
					return false;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
			}
		}
		return false;
	}

	public String saveNewOrders(String strCompId, String strBillId,
			String strItemNo, String strEmpNo, int ispz, String strDate,
			String strTime, String orderPhone, String strCardNo, String openid,
			String strEmpName) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");

		try {
			// 判断是否已经预约
			String strSql = " select count(confirmemp) from orders with(nolock) where orderstate<>2 and confirmemp='"
					+ strEmpNo
					+ "'"
					+ " and confirmdate='"
					+ strDate
					+ "' and orderfacttime='" + strTime + "' ";
			if (amn_Dao.getRowsCount_Ex(strSql) > 0) {
				return "3";
			} else {
				Orders orders = new Orders();
				orders.setCallbillid(strBillId);
				orders.setOrderconply(strCompId);
				orders.setOrderphone(orderPhone);
				orders.setCardno(strCardNo);
				orders.setOrderfactdate(strDate);
				orders.setOrderfacttime(strTime);
				orders.setConfirmdate(CommonTool.getCurrDate());
				orders.setOpenid(openid);
				// orders.setOrderstate(1);
				orders.setOrderstate(0);// 确认中
				orders.setOrderproject(strItemNo);
				orders.setIspz(ispz);
				orders.setConfirmemp(strEmpNo);
				orders.setOrdersid(UUID.randomUUID().toString());
				orders.setStaffname(strEmpName);
				amn_Dao.saveOrUpdate(orders);
				net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
						.fromObject(loadOrdersEmpInfo(strCompId, strEmpNo));
				return jsonarray.toString();
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}
	}

	public List<Staffinfo> loadOrdersEmpInfo(String strCompId, String strEmpNo) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		List<Staffinfo> lStaffinfos = new ArrayList<Staffinfo>();
		String strSql = " select staffno,staffname,manageno,staffopenid from staffinfo where staffno='"
				+ strCompId + strEmpNo + "' and compno='" + strCompId + "'";
		strSql += " union all select staffno,staffname,manageno,staffopenid from staffinfo where compno='"
				+ strCompId + "' and position in('00101','00104') ";
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Staffinfo staffinfo = new Staffinfo();
					staffinfo.setBstaffno(rs.getString("staffno"));
					staffinfo.setStaffename(rs.getString("staffname"));
					staffinfo.setStaffopenid(rs.getString("staffopenid"));
					staffinfo.setManageno(rs.getString("manageno"));
					lStaffinfos.add(staffinfo);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return lStaffinfos;
	}

	public String loadOrderDetal(String openid) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		List<Orders> lsOrders = new ArrayList<Orders>();
		String strSql = " select orderfactdate,orderfacttime,orderstate,compaddress,complydetail,cardno,callbillid,orderconply,staffname,compname,compphone,orderproject,prjname from orders with(nolock),companyinfo with(nolock),projectnameinfo with(nolock) where compno=orderconply and orderproject=prjno and openid='"
				+ openid
				+ "' and confirmdate between '"
				+ CommonTool.addMonth(CommonTool.getCurrDate(), -3)
				+ "' and '"
				+ CommonTool.getCurrDate() + "' ";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			while (rs.next()) {
				Orders orders = new Orders();
				orders.setCallbillid(rs.getString("callbillid"));
				orders.setOrderconply(rs.getString("orderconply"));
				orders.setOrderfactdate(rs.getString("orderfactdate"));
				orders.setComplydetail(rs.getString("complydetail"));
				orders.setOrderfacttime(rs.getString("orderfacttime"));
				orders.setStrCompName(rs.getString("compname"));
				orders.setAddress(rs.getString("compaddress"));
				orders.setOrderstate(rs.getInt("orderstate"));
				orders.setStaffname(rs.getString("staffname"));
				orders.setOrderphone(rs.getString("compphone"));
				orders.setOrderproject(rs.getString("orderproject"));
				orders.setOrderfactproject(rs.getString("prjname"));
				lsOrders.add(orders);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsOrders);
		return jsonarray.toString();
	}

	public String loadOrderById(String callbillid) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		Orders orders = new Orders();
		String strSql = " select callbillid,orderfactdate,orderfacttime,orderstate,cardno,orderconply,staffname,openid,orderphone,orderproject from orders with(nolock) where callbillid='"
				+ callbillid + "'";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			while (rs.next()) {
				orders.setCallbillid(rs.getString("callbillid"));
				orders.setOrderfactdate(rs.getString("orderfactdate"));
				orders.setOrderfacttime(rs.getString("orderfacttime"));
				orders.setOrderstate(rs.getInt("orderstate"));
				orders.setCardno(rs.getString("cardno"));
				orders.setOrderconply(rs.getString("orderconply"));
				orders.setStaffname(rs.getString("staffname"));
				orders.setOpenid(rs.getString("openid"));
				orders.setOrderphone(rs.getString("orderphone"));
				orders.setOrderproject(rs.getString("orderproject"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(orders);
		return jsonarray.toString();
	}

	public String loadOrderByEmp(String strCompId, String strEmpNo,
			String orderDate, String orderTime) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		List<Orders> list = new ArrayList<Orders>();
		String date = "convert(datetime,'" + orderDate + "'+' '+'" + orderTime
				+ "',20)";
		String strSql = " select callbillid,orderfactdate,orderfacttime,confirmemp,staffname,openid from orders with(nolock) where confirmemp='"
				+ strCompId
				+ strEmpNo
				+ "'"
				+ " and convert(datetime,orderfactdate+' '+orderfacttime,20) between "
				+ date + " and dateadd(hour,2," + date + ")";// 查询前选择时间两个小时之内的
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			while (rs.next()) {
				Orders orders = new Orders();
				orders.setCallbillid(rs.getString("callbillid"));
				orders.setOrderfactdate(rs.getString("orderfactdate"));
				orders.setOrderfacttime(rs.getString("orderfacttime"));
				orders.setConfirmemp(rs.getString("confirmemp"));
				orders.setStaffname(rs.getString("staffname"));
				orders.setOpenid(rs.getString("openid"));
				list.add(orders);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(list);
		return jsonarray.toString();
	}

	public String loadOrderByOpenId(String openId, String orderDate,
			String orderTime) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		List<Orders> list = new ArrayList<Orders>();
		String strSql = " select callbillid,orderfactdate,orderfacttime,confirmemp,staffname,openid,orderconply from orders with(nolock) where openid='"
				+ openId
				+ "'"
				+ " and confirmdate ='"
				+ orderDate
				+ "' and orderfacttime='" + orderTime + "' ";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			while (rs.next()) {
				Orders orders = new Orders();
				orders.setCallbillid(rs.getString("callbillid"));
				orders.setOrderfactdate(rs.getString("orderfactdate"));
				orders.setOrderfacttime(rs.getString("orderfacttime"));
				orders.setConfirmemp(rs.getString("confirmemp"));
				orders.setStaffname(rs.getString("staffname"));
				orders.setOpenid(rs.getString("openid"));
				orders.setOrderconply(rs.getString("orderconply"));
				list.add(orders);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(list);
		return jsonarray.toString();
	}

	public String loadNewEmpInfoByComp(String strCompId, String strCardNo,
			int billtype) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strBeforDate = CommonTool.datePlusDay(CommonTool.getCurrDate(),
				-92);
		String strSql = "";
		// 美发
		if (billtype == 1) {
			strSql = "select substring(staffno,4,3) staffno,staffname,manageno ,ccount=COUNT(distinct b.csbillid)"
					+ " from staffinfo a with(nolock),mconsumeinfo b with(nolock),dconsumeinfo c with(nolock)"
					+ " where b.cscompid=c.cscompid and b.csbillid=c.csbillid"
					+ " and b.financedate between '"
					+ strBeforDate
					+ "' and '"
					+ CommonTool.getCurrDate()
					+ "' "
					+ " and b.cscardno='"
					+ strCardNo
					+ "' "
					+ " and ((c.csfirstinid=a.manageno and csfirsttype=1 ) "
					+ " or (c.cssecondinid=a.manageno and cssecondtype=1 )"
					+ " or (c.csthirdinid=a.manageno and csthirdtype=1)) "
					+ " and position in ('003','006','007','00701','00702') and  a.compno='"
					+ strCompId
					+ "' "
					+ " and subtring(staffno,4,6)<>'300' "
					+ " group by staffno,staffname,manageno ";
		} else {
			// 美容
			strSql = "select substring(staffno,4,3) staffno,staffname,manageno ,ccount=COUNT(distinct b.csbillid)"
					+ " from staffinfo a with(nolock),mconsumeinfo b with(nolock),dconsumeinfo c with(nolock)"
					+ " where b.cscompid=c.cscompid and b.csbillid=c.csbillid"
					+ " and b.financedate between '"
					+ strBeforDate
					+ "' and '"
					+ CommonTool.getCurrDate()
					+ "' "
					+ " and b.cscardno='"
					+ strCardNo
					+ "' "
					+ " and ((c.csfirstinid=a.manageno and csfirsttype=1 ) "
					+ " or (c.cssecondinid=a.manageno and cssecondtype=1 )"
					+ " or (c.csthirdinid=a.manageno and csthirdtype=1)) "
					+ " and position in ('004','00401','00402','00103') and  a.compno='"
					+ strCompId
					+ "' "
					+ " and subtring(staffno,4,6)<>'400' "
					+ " group by staffno,staffname,manageno ";
		}
		/*
		 * strSql+=
		 * " union all  select staffno,staffname,manageno ,ccount=COUNT(distinct b.csbillid)"
		 * +
		 * " from staffinfo a with(nolock),mconsumeinfo b with(nolock),dconsumeinfo c with(nolock)"
		 * + " where b.cscompid=c.cscompid and b.csbillid=c.csbillid" +
		 * " and b.financedate between '"
		 * +strBeforDate+"' and '"+CommonTool.getCurrDate()+"' " +
		 * " and b.cscardno='"+strCardNo+"' " +
		 * " and ((c.csfirstinid=a.manageno and csfirsttype=1) " +
		 * " or (c.cssecondinid=a.manageno and cssecondtype=1)" +
		 * " or (c.csthirdinid=a.manageno and csthirdtype=1)) " +
		 * " and position in ('003','004','00401','00402','006','007','00701','00702','00103') "
		 * + " group by staffno,staffname,manageno ";
		 */
		ResultSet rs = null;
		List<Staffinfo> lsStaffinfos = new ArrayList<Staffinfo>();
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Staffinfo staffinfo = new Staffinfo();
					staffinfo.setBstaffno(rs.getString("staffno"));
					staffinfo.setStaffename(rs.getString("staffname"));
					staffinfo.setManageno(rs.getString("manageno"));
					lsStaffinfos.add(staffinfo);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsStaffinfos);
		return jsonarray.toString();
	}

	public String loadStartHairInfo(String strCompId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " select top 3 *,newid() random from staffinfo where position in('006','007','00701','00702') and curstate='2' and compno='"
				+ strCompId + "' order by random ";
		ResultSet rs = null;
		List<Staffinfo> lsStaffinfos = new ArrayList<Staffinfo>();
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Staffinfo staffinfo = new Staffinfo();
					staffinfo.setBstaffno(rs.getString("staffno"));
					staffinfo.setStaffename(rs.getString("staffname"));
					staffinfo.setManageno(rs.getString("manageno"));
					lsStaffinfos.add(staffinfo);
				}
			}
			if (lsStaffinfos != null && lsStaffinfos.size() > 0) {
				for (int i = 0; i < lsStaffinfos.size(); i++) {
					lsStaffinfos.get(i).setBstaffno(
							lsStaffinfos.get(i).getBstaffno().substring(3));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsStaffinfos);
		return jsonarray.toString();
	}

	public String loadStartBeautyInfo(String strCompId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " select top 3 *,newid() random from staffinfo where position in('004','00401','00103') and curstate='2' and compno='"
				+ strCompId
				+ "' and substring(staffno,4,6)<>'400' order by random ";
		ResultSet rs = null;
		List<Staffinfo> lsStaffinfos = new ArrayList<Staffinfo>();
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					Staffinfo staffinfo = new Staffinfo();
					staffinfo.setBstaffno(rs.getString("staffno"));
					staffinfo.setStaffename(rs.getString("staffname"));
					staffinfo.setManageno(rs.getString("manageno"));
					lsStaffinfos.add(staffinfo);
				}
			}
			if (lsStaffinfos != null && lsStaffinfos.size() > 0) {
				for (int i = 0; i < lsStaffinfos.size(); i++) {
					lsStaffinfos.get(i).setBstaffno(
							lsStaffinfos.get(i).getBstaffno().substring(3));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsStaffinfos);
		return jsonarray.toString();
	}

	public String checkUser(String strCompId, String userName, String pwd) {

		RepsonBean req = new RepsonBean(false);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		DESPlus desPlus;
		String strPwd = "";
		try {
			desPlus = new DESPlus();
			strPwd = desPlus.encrypt(pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}

		if (checkCompId(strCompId)) {

			String strSql = "select count(userno) from sysuserinfo where userno='"
					+ userName + "' and userpwd='" + strPwd + "' ";
			try {
				if (amn_Dao.getRowsCount_Ex(strSql) > 0) {
					req.setbRet(true);
				}
			} catch (DataAccessException e) {
				e.printStackTrace();
			} catch (RuntimeException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray.fromObject(req);
		return jsonarray.toString();

	}

	public String saveInventory(String strCompId, String userId,
			String strParams) {
		RepsonBean req = new RepsonBean(false);
		if (CommonTool.checkStr(strCompId) == false
				|| CommonTool.checkStr(strParams) == false) {

		} else {
			AMN_Dao amn_Dao = null;
			SpringDBInit sdbinit = SpringDBInit.getInstance();
			amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
			net.sf.json.JSONArray jsonArray = net.sf.json.JSONArray
					.fromObject(strParams);
			List<Inventorydetal> lsInventorydetals = net.sf.json.JSONArray
					.toList(jsonArray, Inventorydetal.class);
			try {
				InventoryId id = new InventoryId();
				Inventory inventory = new Inventory();
				id.setCompid(strCompId);
				id.setBillid(UUID.randomUUID().toString());
				id.setInvenbill(createInvBill(strCompId));
				inventory.setId(id);
				inventory.setCreatedate(CommonTool.getCurrDate());
				inventory.setState(1);
				inventory.setUserid(userId);
				for (Inventorydetal inventorydetal : lsInventorydetals) {
					InventorydetalId detalId = new InventorydetalId();
					detalId.setBillid(id.getBillid());
					detalId.setSerial(UUID.randomUUID().toString());
					detalId.setCompid(strCompId);
					inventorydetal.setId(detalId);
				}
				amn_Dao.save(inventory);
				amn_Dao.saveOrUpdateAll(lsInventorydetals);
				req.setbRet(true);
			} catch (Exception e) {
				req.setbRet(false);
				e.printStackTrace();
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray.fromObject(req);
		return jsonarray.toString();
	}

	public String addInventory(String strCompId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " select invenbill from inventory where compid='"
				+ strCompId + "' and state=1 group by invenbill ";
		ResultSet rs = null;
		String invenbill = "";
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					invenbill = rs.getString("invenbill");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return invenbill;
	}

	public String createInvBill(String strCompId) {
		String invBill = addInventory(strCompId);
		if (CommonTool.checkStr(invBill)) {
			return invBill;
		}
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " select count(ccount) num from( select invenbill,count(invenbill) ccount from inventory where compid='"
				+ strCompId + "' group by invenbill) b  ";
		ResultSet rs = null;

		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					invBill = strCompId + (rs.getInt("num") + 1);
				}
			}
			if (CommonTool.checkStr(invBill) == false) {
				invBill = strCompId + "1";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return invBill;
	}

	public String checkGoodBar(String strCompId, String strGoodBar) {
		RepsonBean req = new RepsonBean(false);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " select * from dgoodsbarinfo where goodsbarno='"
				+ strGoodBar + "' and receivestore='" + strCompId
				+ "' and barnostate=2 ";
		Dgoodsbarinfo dgoodsbarinfo = null;
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {

					dgoodsbarinfo = new Dgoodsbarinfo();
					dgoodsbarinfo.setBgoodsbarno(rs.getString("goodsbarno"));
					dgoodsbarinfo.setBgoodsno(rs.getString("goodsno"));
					req.setbRet(true);
					req.setObjJson(net.sf.json.JSONArray.fromObject(
							dgoodsbarinfo).toString());
				}
			}
			if (dgoodsbarinfo == null) {
				req.setStrMessage("物品条码不存在或者是状态不对,或者是不是本店的条形码");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		JsonConfig config = new JsonConfig();
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray.fromObject(req);
		return jsonarray.toString();
	}

	public String deleteInventoryByInventBill(String strCompId,
			String inventBill) {
		RepsonBean req = new RepsonBean(false);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " delete inventory where invenbill='" + inventBill
				+ "' and compid='" + strCompId + "' ";
		try {
			req.setbRet(amn_Dao.executeSql(strSql));
		} catch (Exception e) {
			e.printStackTrace();
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray.fromObject(req);
		return jsonarray.toString();
	}

	public String deleteInventoryByBill(String strCompId, String bill) {
		RepsonBean req = new RepsonBean(false);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " delete inventory where billid='" + bill
				+ "' and compid='" + strCompId + "' ";
		try {
			req.setbRet(amn_Dao.executeSql(strSql));
		} catch (Exception e) {
			e.printStackTrace();
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray.fromObject(req);
		return jsonarray.toString();
	}

	public String loadGoodsBarInfo(String goodsBar) {
		RepsonBean req = new RepsonBean(false);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " select * from dgoodsbarinfo where goodsbarno='"
				+ goodsBar + "' ";
		Dgoodsbarinfo dgoodsbarinfo = null;
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {

					dgoodsbarinfo = new Dgoodsbarinfo();
					dgoodsbarinfo.setBgoodsbarno(rs.getString("goodsbarno"));
					dgoodsbarinfo.setBgoodsno(rs.getString("goodsno"));
					dgoodsbarinfo.setBarnostate(rs.getInt("barnostate"));
					req.setbRet(true);
					req.setObjJson(net.sf.json.JSONArray.fromObject(
							dgoodsbarinfo).toString());
				}
			}
			if (dgoodsbarinfo == null) {
				req.setStrMessage("物品条码不存在或者是状态不对,或者是不是本店的条形码");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray.fromObject(req);
		return jsonarray.toString();
	}

	public boolean checkCompId(String strCompId) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " select count(compno) from Companyinfo where compno='"
				+ strCompId + "' ";
		try {
			if (amn_Dao.getRowsCount_Ex(strSql) > 0) {
				return true;
			}
		} catch (DataAccessException e) {
			e.printStackTrace();
		} catch (RuntimeException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public String findGoodBarInfo(String goodBarNo) {
		RepsonBean req = new RepsonBean(false);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select * from dgoodsbarinfo a,goodsnameinfo b where a.goodsno=b.goodsno and a.goodsbarno='"
				+ goodBarNo + "' ";
		ResultSet rs = null;
		Dgoodsbarinfo dgoodsbarinfo = null;

		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					dgoodsbarinfo = new Dgoodsbarinfo();
					switch (rs.getInt("barnostate")) {
					case 0:
						dgoodsbarinfo.setBarnostateText("生成");
						break;
					case 1:
						dgoodsbarinfo.setBarnostateText("入库");
						break;
					case 2:
						dgoodsbarinfo.setBarnostateText("发货/出库");
						break;
					case 3:
						dgoodsbarinfo.setBarnostateText("销售");
						break;
					case 4:
						dgoodsbarinfo.setBarnostateText("消耗");
						break;
					case 5:
						dgoodsbarinfo.setBarnostateText("损坏");
						break;
					case 6:
						dgoodsbarinfo.setBarnostateText("退货中");
						break;
					}
					dgoodsbarinfo.setBgoodsbarno(rs.getString("goodsbarno"));
					dgoodsbarinfo.setCreatedate(CommonTool.getDateMask(rs
							.getString("createdate")));
					dgoodsbarinfo.setCreatestaffno(rs
							.getString("createstaffno"));
					dgoodsbarinfo.setInserdate(CommonTool.getDateMask(rs
							.getString("inserdate")));
					dgoodsbarinfo.setInserbillno(rs.getString("inserbillno"));
					dgoodsbarinfo.setOuterdate(CommonTool.getDateMask(rs
							.getString("outerdate")));
					dgoodsbarinfo.setOuterbill(rs.getString("outerbill"));
					dgoodsbarinfo.setReceivestore(rs.getString("receivestore"));
					dgoodsbarinfo.setCostdate(CommonTool.getDateMask(rs
							.getString("costdate")));
					dgoodsbarinfo.setCostbillo(rs.getString("costbillo"));
					dgoodsbarinfo.setCoststore(rs.getString("coststore"));
					dgoodsbarinfo.setProddate(CommonTool.getDateMask(rs
							.getString("proddate")));
					dgoodsbarinfo.setGoodsText(rs.getString("goodsname"));
					dgoodsbarinfo.setBgoodsno(rs.getString("goodsno"));
					req.setbRet(true);
					req.setObjJson(net.sf.json.JSONArray.fromObject(
							dgoodsbarinfo).toString());

				}

			}
			if (dgoodsbarinfo == null) {
				req.setStrMessage("该条形码不存在");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return net.sf.json.JSONArray.fromObject(req).toString();
	}
	
	public Map<String,Object> loadWchatProduct(String productId){
		ResultSet rs = null;
		Map<String, Object> map = new HashMap<String, Object>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		AMN_Dao amn_Dao = amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select * from wx_product where id="+ productId;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					map.put("id", rs.getInt("id"));
					map.put("cid", rs.getInt("cid"));
					map.put("name", rs.getString("name"));
					map.put("price", rs.getFloat("price"));
					map.put("num", rs.getInt("num"));
					map.put("type", rs.getInt("type"));
					map.put("catgory", rs.getInt("catgory"));
					map.put("group", rs.getInt("group"));
					map.put("sumnum", rs.getInt("sumnum"));
					map.put("originalcost", rs.getFloat("originalcost"));
					map.put("card_id", rs.getString("card_id"));
					map.put("content", rs.getString("content"));
					map.put("ishot", rs.getBoolean("ishot"));
					map.put("intro", rs.getString("intro"));
					map.put("mainimageurl", rs.getString("mainimageurl"));
					map.put("sort", rs.getInt("sort"));
					map.put("minute", rs.getInt("minute"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return map;
	}
	
	public List<Map<String,Object>> loadProductDetail(String productId){
		ResultSet rs = null;
		Map<String, Object> map = null;
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		AMN_Dao amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "SELECT imageDescription, imageurl, id FROM wx_product_detail "
				+ "WHERE wx_product_detail.parentId="+ productId +" ORDER BY id";
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					map = new HashMap<String, Object>();
					map.put("id", rs.getInt("id"));
					map.put("imageDescription", rs.getString("imageDescription"));
					map.put("imageurl", rs.getString("imageurl"));
					list.add(map);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	public List<Map<String,Object>> loadProductPackage(String productId){
		ResultSet rs = null;
		Map<String, Object> map = null;
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		AMN_Dao amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "SELECT wx_product_package.count, wx_product.name, wx_product.price, wx_product.mainimageurl, "
				+ "wx_product.type, wx_product_package.productid FROM wx_product_package INNER JOIN wx_product ON "
				+ "wx_product_package.productid = wx_product.id where wx_product_package.parentId="+ productId;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					map = new HashMap<String, Object>();
					map.put("productid", rs.getInt("productid"));
					map.put("count", rs.getInt("count"));
					map.put("name", rs.getString("name"));
					map.put("price", rs.getFloat("price"));
					map.put("type", rs.getInt("type"));
					map.put("mainimageurl", rs.getString("mainimageurl"));
					list.add(map);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	public List<Works> pageWorks(int begin,int end,double xcoordinate,double ycoordinate){
		List<Works> res = new ArrayList<Works>();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String strSql =  "with  tmp as( 	SELECT "
				+ " staffHairDesignerInfo.id,staffHairDesignerInfo.name, staffHairDesignerInfo.imageurl, "
				+ " staffHairDesignerInfo.content, staffHairDesignerInfo.ordernum, staffHairDesignerInfo.praisenum, "
				+ " staffHairDesignerInfo.label2, staffinfo.staffname,staffinfo.staffename, staffinfo.compno,companyinfo.compipaddress, "
				+ " companyinfo.compname,staffinfo.staffno, staffinfo.staffopenid, staffinfo.imageurl AS staffimageurl, "
				+ " 5 as starnum, "
				+ "    (case staffinfo.position "
				+ "    when '003' then '美发师' "
				+ "    when '006' then '首席' "
				+ "    when '007' then '总监' "
				+ "    when '00701' then '创意总监' "
				+ "    when '00702' then '店长' else ''  end) as position, "
				+ "     sysparaminfo.paramvalue as price, 'prCjajpXHPvayBLLMtFsDRRAiXs8' as card_id "
				+ "FROM  "
				+ " staffHairDesignerInfo INNER JOIN "
				+ " staffinfo ON staffHairDesignerInfo.manageno = staffinfo.manageno "
				+ "    inner join companyinfo on companyinfo.compno = staffinfo.compno "
				+ "    left join sysparaminfo on sysparaminfo.compid=staffinfo.compno and  "
				+ "    ((staffinfo.position ='003' and sysparaminfo.paramid='SP075')  "
				+ "    or (staffinfo.position ='006' and sysparaminfo.paramid='SP076') "
				+ "    or (staffinfo.position ='007' and sysparaminfo.paramid='SP077') "
				+ "    or (staffinfo.position ='00701' and sysparaminfo.paramid='SP078') "
				+ "    or (staffinfo.position ='00702' and sysparaminfo.paramid='SP079') ) "
				+ " where companyinfo.compno in (select compno from (select top 3000  companyinfo.compno, abs(cast(xcoordinate as float) -"+xcoordinate+")  + abs(cast( ycoordinate as float) -"+ycoordinate+") as y from companyinfo where xcoordinate is not null order  by y	)as cp)"
				+ ")  "
				+"                    "
				+"    select tmp2.row ,tmp.* from tmp, "
				+"    (select row_number() OVER (order by id) row, id from tmp)tmp2  where tmp.id=tmp2.id and row > "+begin+" and row <="+end+" ";
		try{
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					
					int id = rs.getInt("id");
					String name= rs.getString("staffename");
					String imageurl = rs.getString("imageurl");
					String content = clobToStr(rs.getClob("content"));
					int ordernum = rs.getInt("ordernum");
					
					int praisenum = rs.getInt("praisenum");
					//String label2 = rs.getObject("label2");
					String staffname = rs.getString("staffname");
					String compno = rs.getString("compno");
					String compipaddress = rs.getString("compipaddress");
					
					String compname = rs.getString("compname");
					String staffno = rs.getString("staffno");
					String staffopenid = rs.getString("staffopenid");
					String staffimageurl = rs.getString("staffimageurl");
					int starnum = rs.getInt("starnum");
					String position = rs.getString("position");
					double price = rs.getDouble("price");
//					String staffopenid = rs.getString("staffopenid");
					//String card_id = rs.getObject("card_id");
					Works tmp = new Works(id,staffimageurl,imageurl,name,position,price,praisenum,ordernum,staffopenid);
					res.add(tmp);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return res;
	}
	public Works loadWorks(int id){
		Works works = null;
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String strSql =  "with  tmp as( 	SELECT "
				+ " staffHairDesignerInfo.id,staffHairDesignerInfo.name, staffHairDesignerInfo.imageurl, "
				+ " staffHairDesignerInfo.content, staffHairDesignerInfo.ordernum, staffHairDesignerInfo.praisenum, "
				+ " staffHairDesignerInfo.label2, staffinfo.staffname,staffinfo.staffename, staffinfo.compno,companyinfo.compipaddress, "
				+ " companyinfo.compname,staffinfo.staffno, staffinfo.staffopenid, staffinfo.imageurl AS staffimageurl, "
				+ " 5 as starnum, "
				+ "    (case staffinfo.position "
				+ "    when '003' then '美发师' "
				+ "    when '006' then '首席' "
				+ "    when '007' then '总监' "
				+ "    when '00701' then '创意总监' "
				+ "    when '00702' then '店长' else ''  end) as position, "
				+ "     sysparaminfo.paramvalue as price, 'prCjajpXHPvayBLLMtFsDRRAiXs8' as card_id "
				+ "FROM  "
				+ " staffHairDesignerInfo INNER JOIN "
				+ " staffinfo ON staffHairDesignerInfo.manageno = staffinfo.manageno "
				+ "    inner join companyinfo on companyinfo.compno = staffinfo.compno "
				+ "    left join sysparaminfo on sysparaminfo.compid=staffinfo.compno and  "
				+ "    ((staffinfo.position ='003' and sysparaminfo.paramid='SP075')  "
				+ "    or (staffinfo.position ='006' and sysparaminfo.paramid='SP076') "
				+ "    or (staffinfo.position ='007' and sysparaminfo.paramid='SP077') "
				+ "    or (staffinfo.position ='00701' and sysparaminfo.paramid='SP078') "
				+ "    or (staffinfo.position ='00702' and sysparaminfo.paramid='SP079') ) "
				+ "	)  "
				+"                    "
				+"    select tmp.* from tmp where id = "+id;
		try{
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					int tid = rs.getInt("id");
					String name= rs.getString("staffename");
					String imageurl = rs.getString("imageurl");
					String content = clobToStr(rs.getClob("content"));
					int ordernum = rs.getInt("ordernum");
					
					int praisenum = rs.getInt("praisenum");
					String label2 = rs.getString("label2");
					String staffname = rs.getString("staffename");
					String compno = rs.getString("compno");
					String compipaddress = rs.getString("compipaddress");
					
					String compname = rs.getString("compname");
					String staffno = rs.getString("staffno");
					String staffopenid = rs.getString("staffopenid");
					String staffimageurl = rs.getString("staffimageurl");
					int starnum = rs.getInt("starnum");
					String position = rs.getString("position");
					double price = rs.getDouble("price");
					
					String staffHairDesignerInfo_id = rs.getObject("id")+"";
					String staffinfo_staffno = rs.getObject("staffno")+"";
					String staffinfo_staffopenid = rs.getObject("staffopenid")+"";
					String wx_card_price = rs.getObject("price")==null?"0":rs.getObject("price")+"";
					String wx_card_card_id = rs.getObject("card_id")==null?"0":rs.getObject("card_id")+"";;
					String staffinfo_staffname = rs.getObject("staffename")+"";
					String staffinfo_compno = rs.getObject("compno")+"";
					String companyinfo_compipaddress = rs.getObject("compipaddress")+"";
					String companyinfo_compname = rs.getObject("compname")+"";
					//String card_id = rs.getObject("card_id");
					works = new Works( id,  staffimageurl,  imageurl,  name,
							 position,  price,  praisenum,  ordernum,  content,
							 label2,  staffopenid,  staffHairDesignerInfo_id,
							 staffinfo_staffno,  staffinfo_staffopenid,
							 wx_card_price,  wx_card_card_id,
							 staffinfo_staffname,  staffinfo_compno,
							 companyinfo_compipaddress,  companyinfo_compname);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return works;
	}
	
	public List<Works> pageWorksByDesigner(int begin,int end,String staffopenid){
		List<Works> res = new ArrayList<Works>();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String strSql = "with  tmp as( "
					+ "SELECT  "
					+ " staffHairDesignerInfo.id,staffHairDesignerInfo.name, staffHairDesignerInfo.imageurl,"
					+ " staffHairDesignerInfo.content, staffHairDesignerInfo.ordernum, staffHairDesignerInfo.praisenum,"
					+ " staffHairDesignerInfo.label2, staffinfo.staffname,staffinfo.staffename, staffinfo.compno,companyinfo.compipaddress,"
					+ " companyinfo.compname,staffinfo.staffno, staffinfo.staffopenid, staffinfo.imageurl AS staffimageurl,staffHairDesignerInfo.createdate,"
					+ " 5 as starnum,"
					+ "    (case staffinfo.position"
					+ "    when '003' then '美发师'"
					+ "    when '006' then '首席'  "
					+ "    when '007' then '总监'  "
					+ "    when '00701' then '创意总监'"
					+ "    when '00702' then '店长' else ''  end) as position,"
					+ "     wx_card.price,wx_card.card_id"
					+ " FROM    "
					+ " staffHairDesignerInfo INNER JOIN"
					+ " staffinfo ON staffHairDesignerInfo.staffopenid = staffinfo.staffopenid"
					+ "    inner join companyinfo on companyinfo.compno = staffinfo.compno"
					+ "    left join wx_card on wx_card.compid=staffinfo.compno and"
					+ "    ((staffinfo.position ='003' and wx_card.paramid='SP075')"
					+ "    or (staffinfo.position ='006' and wx_card.paramid='SP076')"
					+ "    or (staffinfo.position ='007' and wx_card.paramid='SP077')"
					+ "    or (staffinfo.position ='00701' and wx_card.paramid='SP078')"
					+ "    or (staffinfo.position ='00702' and wx_card.paramid='SP079') )"
					+ " where staffHairDesignerInfo.staffopenid = '" + staffopenid + "'" 
					+ ")"
					+ "select tmp2.row ,tmp.* from tmp,(select  row_number() OVER (order by id) row, id from tmp)tmp2  where tmp.id=tmp2.id and row between " + begin + " and " + end + " order by createdate";
		try{
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					int id = rs.getInt("id");
					String name= rs.getString("staffename");
					String imageurl = rs.getString("imageurl");
					String content = clobToStr(rs.getClob("content"));
					int ordernum = rs.getInt("ordernum");
					
					int praisenum = rs.getInt("praisenum");
					//String label2 = rs.getObject("label2");
					String staffname = rs.getString("staffname");
					String compno = rs.getString("compno");
					String compipaddress = rs.getString("compipaddress");
					
					String compname = rs.getString("compname");
					String staffno = rs.getString("staffno");
					//String staffopenid = rs.getString("staffopenid");
					String staffimageurl = rs.getString("staffimageurl");
					int starnum = rs.getInt("starnum");
					String position = rs.getString("position");
					double price = rs.getDouble("price");
					//String staffopenid = rs.getString("staffopenid");
					//String card_id = rs.getObject("card_id");
					Works tmp = new Works(id,staffimageurl,imageurl,name,position,price,praisenum,ordernum,staffopenid);
					res.add(tmp);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return res;
	}
	//到店页面美容项目数据获取
	public List<Project> loadAllProject(){
		List<Project> res = new ArrayList<Project>();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String strSql = "SELECT   wx_product.id,wx_product.type, "
				+" wx_product.name , wx_product.cid, wx_product.price, wx_product.originalcost, wx_product.type,"
				+" wx_product.card_id, wx_product.content, wx_product.ishot, wx_product.intro, wx_product.mainimageurl,"
				+" wx_product.sort, wx_product.sumnum, wx_product.isopen,wx_product.minute "
				+" FROM wx_product "
				+" where isopen=1 and catgory=1 ";
		try{
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					int id = rs.getInt("id");
					String name = rs.getString("name");
					int type = rs.getInt("type");
					Double price = rs.getDouble("price");
					Double originalcost = rs.getDouble("originalcost");
					String content = rs.getString("content");
					String mainimageurl = rs.getString("mainimageurl");
					String intro = rs.getString("intro");
					Integer sumnum = rs.getInt("sumnum");
					Integer minute = rs.getInt("minute");
					Project tmp = new Project(id,name,mainimageurl,45,intro,price,originalcost, type);
					res.add(tmp);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return res;
	}
	
	//到店页面美容项目数据获取
	public List<Project> loadProjectByType(String type){
		List<Project> res = new ArrayList<Project>();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String strSql = "SELECT   wx_product.id,wx_product.type, "
				+" wx_product.name , wx_product.cid, wx_product.price, wx_product.originalcost, wx_product.type,"
				+" wx_product.card_id, wx_product.content, wx_product.ishot, wx_product.intro, wx_product.mainimageurl,"
				+" wx_product.sort, wx_product.sumnum, wx_product.isopen,wx_product.minute "
				+" FROM wx_product "
				+" where isopen=1 and catgory=1  and type="+type;
		try{
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					int id = rs.getInt("id");
					String name = rs.getString("name");
					int type1 = rs.getInt("type");
					Double price = rs.getDouble("price");
					Double originalcost = rs.getDouble("originalcost");
					String content = rs.getString("content");
					String mainimageurl = rs.getString("mainimageurl");
					String intro = rs.getString("intro");
					Integer sumnum = rs.getInt("sumnum");
					Integer minute = rs.getInt("minute");
					Project tmp = new Project(id,name,mainimageurl,45,intro,price,originalcost, type1);
					res.add(tmp);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return res;
	}
	public Designer loadDesigner(String staffopenid){
		Designer res = new Designer();
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String strSql = " SELECT staffinfo.staffno,staffinfo.staffopenid, staffinfo.imageurl AS staffimageurl, staffinfo.staffename,"
				+"        staffinfo.description,staffinfo.compno,companyinfo.compipaddress,companyinfo.compname,            "
				+"        companyinfo.xcoordinate,companyinfo.ycoordinate,                                                    "
				+"       5 as starnum,                                                                                      "
				+"   (case staffinfo.position                                                                                 "
				+"   when '003' then '美发师'                                                                                 "
				+"   when '006' then '首席'                                                                                   "
				+"   when '007' then '总监'                                                                                   "
				+"   when '00701' then '创意总监'                                                                             "
				+"   when '00702' then '店长' else ''  end) as position,                                                      "
				+"   wx_card.price,wx_card.card_id                                                                        "
				+"                                                                                                            "
				+" FROM    staffinfo                                                                                          "
				+"     inner join companyinfo on companyinfo.compno = staffinfo.compno                                        "
				+"     left join wx_card on wx_card.compid=staffinfo.compno and                                           "
				+"     ((staffinfo.position ='003' and wx_card.paramid='SP075')                                           "
				+"     or (staffinfo.position ='006' and wx_card.paramid='SP076')                                         "
				+"     or (staffinfo.position ='007' and wx_card.paramid='SP077')                                         "
				+"     or (staffinfo.position ='00701' and wx_card.paramid='SP078')                                       "
				+"        or (staffinfo.position ='00702' and wx_card.paramid='SP079') )                                  "
				+" where  staffinfo.position in('003','006','007','00701','00702')                                            "
				+"   and substring(staffno,4,6)<>'300'                                                                        "
				+" and staffinfo.staffopenid='"+staffopenid+"'";
		try{
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()) {
					String staffno = rs.getString("staffno");
//					String staffopenid = rs.getString("staffopenid");
					String staffimageurl = rs.getString("staffimageurl");
					String staffname = rs.getString("staffename");
					String description = rs.getString("description");
					String position = rs.getString("position");
					String compno = rs.getString("compno");
					String compipaddress = rs.getString("compipaddress");
					String compname = rs.getString("compname");
//					String xcoordinate = rs.getString("xcoordinate");
//					String ycoordinate = rs.getString("ycoordinate");
					res = new Designer(staffopenid,staffimageurl, staffname, position, 5,
							5, 5, "",  description, compname);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return res;
	}
	
	public static String clobToStr(Clob clob) throws SQLException, IOException {
		String reString = "";
		if(clob != null && clob instanceof Clob){
			Reader is = clob.getCharacterStream();// 得到流
			BufferedReader br = new BufferedReader(is);
			String s = br.readLine();
			StringBuffer sb = new StringBuffer();
			while (s != null) {// 执行循环将字符串全部取出付值给 StringBuffer由StringBuffer转成STRING
				sb.append(s);
				s = br.readLine();
			}
			reString = sb.toString();
		}
		return reString;
	}

	public boolean bindingCard(String cardno, String name,
			String phone, String openid) {
		String sqlFind = "select * from memberinfo where openid = '"+openid+"'";
		String sql = "insert into memberinfo(membervesting,memberno,membercreatedate,membername,membertphone,membermphone,openid)"
				+ "values('001','"+cardno+"',CONVERT(varchar(12) , getdate(), 112 ),'"+name+"','"+phone+"','"+phone+"','"+openid+"')";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			rs = amn_Dao.executeQuery(sqlFind);
			if(rs.next()){
				rs2 = amn_Dao.executeQuery("select * from memberinfo where openid is not null and memberno = '"+cardno+"'");
				if(rs2.next()){
					return true;
				}
				sql = "update memberinfo set openid = '"+openid+"' where  memberno = '"+cardno+"' and membermphone = '"+phone+"' and membername = '"+name+"'";
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return amn_Dao.executeSql(sql);
	}
	
	public Staffinfo findStaffinfoByOpenId(String openid){
		Staffinfo aStaffinfo = null;
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = " From Staffinfo where staffopenid='" + openid + "'";
		List<Staffinfo> lsStaffinfos = null;
		try {
			lsStaffinfos = amn_Dao.findByHql(strSql);
			if (lsStaffinfos != null && lsStaffinfos.size() > 0) {
				aStaffinfo = lsStaffinfos.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return aStaffinfo;
	}


	@Override
	public boolean saveStaffHairDesignerInfo(Integer id, String lx,
			String bq, String ms, String openid,
			String imageUrl) {
		String sql = "INSERT INTO staffHairDesignerInfo (name,staffopenid,imageurl,content,ordernum"
					+ ",praisenum,createdate,lable,label2) values("
					+ "'"+ bq + "','"+ openid + "','"+imageUrl+"','"+ms+"',0,0,null,null,'"+lx+"')";
		if(id!=null&&id>0){
			sql = "update staffHairDesignerInfo set name ='"+bq+"' and imageurl = '"+imageUrl+"' and content = '"+ms+"' and lable2 ='"+lx+"' where id = "+id.intValue();
		}
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		return amn_Dao.executeSql(sql);
	}
	
	public List<StaffHairDesignerInfo> loadStaffHairDesignerInfo(String openid){
		List<StaffHairDesignerInfo> res = new ArrayList<StaffHairDesignerInfo>();
		String strSql = "select id,name,staffopenid,imageurl,content,"
				+ "ordernum,praisenum,createdate,lable,label2 from staffHairDesignerInfo where staffopenid = '"+openid+"'";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		rs = amn_Dao.executeQuery(strSql);
		try{
			if (rs != null) {
				while (rs.next()) {
					
					Integer id = rs.getInt("id");
					String name= rs.getString("name");
					String imageurl = rs.getString("imageurl");
					String content = rs.getString("content");
					Integer ordernum = rs.getInt("ordernum");
					
					Integer praisenum = rs.getInt("praisenum");
					String staffopenid = rs.getString("staffopenid");
					String lable = rs.getString("lable");
					String lable2 = rs.getString("label2");
					StaffHairDesignerInfo tmp = new StaffHairDesignerInfo(id, name, staffopenid, imageurl, content, ordernum, praisenum, null, lable, lable2);
					res.add(tmp);
				}
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		return res;
	}
	
	public StaffHairDesignerInfo findStaffHairDesignerInfo(Integer id){
		StaffHairDesignerInfo res = new StaffHairDesignerInfo();
		String strSql = "select id,name,staffopenid,imageurl,content,"
				+ "ordernum,praisenum,createdate,lable,label2 from staffHairDesignerInfo where id = '"+id.intValue()+"'";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		rs = amn_Dao.executeQuery(strSql);
		try{
			if (rs != null && rs.next()) {
				String name= rs.getString("name");
				String imageurl = rs.getString("imageurl");
				String content = clobToStr(rs.getClob("content"));
				Integer ordernum = rs.getInt("ordernum");
				Integer praisenum = rs.getInt("praisenum");
				String staffopenid = rs.getString("staffopenid");
				String lable = rs.getString("lable");
				String lable2 = rs.getString("label2");
				res = new StaffHairDesignerInfo(id, name, staffopenid, imageurl, content, ordernum, praisenum, null, lable, lable2);
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		return res;
	}
	
	public boolean updateStaffInfo(String openid,String staffename,String imageurl){
		boolean res = false;
		String sql = "update staffinfo set staffename ='"+staffename+"' , imageurl = '"+imageurl+"' where staffopenid = '"+openid+"'";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		return amn_Dao.executeSql(sql);
	}
	
	public VirtualCard saveVirtualCard(String openId,String phone,int sex){
		VirtualCard aVirtualCard = new VirtualCard();
		aVirtualCard.setOpenid(openId);
		aVirtualCard.setPhone(phone);
		aVirtualCard.setSex(sex);
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		try {
			amn_Dao.save(aVirtualCard);
			aVirtualCard.setCardno("0"+(39000000000L+aVirtualCard.getId()));
			amn_Dao.saveOrUpdate(aVirtualCard);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return aVirtualCard;
	}


	@Override
	public String getStaffWorkDay(String beign,
			String end, String openId) {
		String res = "";
		String sql = " with tmp as( "
				+" select confirmemp as staffno, cast( orderfactdate as date)  as [date], "
				+" cast(substring(orderfacttime,1,2) + ':' +substring(orderfacttime,3,2) +':'+ substring(orderfacttime,4,2) as time) as [time] ,1 as isWork from orders  "
				+" union "
				+" select staffno, restday as [date],''  as [time],0 as iswork from staffinfo_restday  "
				+" ) "
				+" select * from tmp where >= '"+beign+"' and date < '"+end+"' "
				+" and staffno = (select top 1 staffno from staffinfo where staffopenid = '"+openId+"') ";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		Calendar c1=Calendar.getInstance() ;
		try {
			ResultSet rs = amn_Dao.executeQuery(sql);
			if(rs!=null){
				while(rs.next()){
					Date date = rs.getDate("date");
					if(date!=null){
						if(res.length()>0){
							res+=",";
						}
						c1.setTime(date);
						res += c1.get(Calendar.DAY_OF_MONTH);
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return res;
	}


	@Override
	public boolean saveStaffRestDay(String openId,
			String date) {
		String strSql = "Insert into staffinfo_restday (staffno,restday) select top 1 staffno,'"+date+"' from staffinfo where staffopenid = '"+openId+"'";
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		return amn_Dao.executeSql(strSql);
	}


	@Override
	public String getOrders(String beign,String end,
			String openid) {
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		List<Orders> lsOrders = new ArrayList<Orders>();
		String strSql = "from Orders "
					+ "where "
					//+ "and confirmemp=(select staffno from staffinof where staffopenid='"+ openid+ "')"
					+ "  orderfactdate >= '"+beign+"' and orderfactdate < '"+end+"' order by orderfacttime";
		try {
			lsOrders = amn_Dao.findByHql(strSql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
		net.sf.json.JSONArray jsonarray = net.sf.json.JSONArray
				.fromObject(lsOrders);
		return jsonarray.toString();
	}
	
	private String getString(String value) {
		return value==null?"":value;
		
	}
	
	//查询会员账户绑定的卡号
	public String loadAllCard(String strCompId, String openId) {
		AMN_Dao amn_Dao = null;
		Cardinfo returnValue = null;
		StringBuffer card = new StringBuffer();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strModeId = this.loadSysParam(strCompId, "SP063");
		String strFristModeId = "";// 第一级模板Id
		String strSecondModeId = "";// 第2级模板ID
		String strThirthModeId = "";// 第三级模板Id
		// 先定位模板门店的连锁级别(暂时支持4级连锁)
		int compLvl = this.loadCompLvl(strCompId);
		if (compLvl == 2) {
			strFristModeId = this.loadparentModeId(strModeId);
		} else if (compLvl == 3) {
			strSecondModeId = this.loadparentModeId(strModeId);
			if (!strSecondModeId.equals(""))
				strFristModeId = this.loadparentModeId(strSecondModeId);
		} else if (compLvl == 4) {
			strThirthModeId = this.loadparentModeId(strModeId);
			if (!strThirthModeId.equals(""))
				strSecondModeId = this.loadparentModeId(strThirthModeId);
			if (!strSecondModeId.equals(""))
				strFristModeId = this.loadparentModeId(strSecondModeId);
		}
		String strSql = "select a.cardno "
				+ " from cardaccount c with(nolock),cardtypeinfo d with(nolock), cardinfo a with(nolock) left join memberinfo b with(nolock) on  a.cardno=b.memberno where "
				+ " a.cardno=c.cardno and b.openid='"+ openId + "' and cardstate in(4,5) and a.cardtype=d.cardtypeno and cardtypemodeid in ('"+ strModeId+ "','"+ strFristModeId+ "','"+ strSecondModeId+ "','"+ strThirthModeId+ "')"
				+ " group by a.cardno,salecarddate order by salecarddate";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				while (rs.next()){
					card.append("@").append(rs.getString("cardno"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return card.length()>0?StringUtils.substring(card.toString(), 1):"";
	}
	
	//我的账户信息
	public Cardinfo loadCardInfobyOpenid(String strCompId, String openId, String strCardNo) {
		AMN_Dao amn_Dao = null;
		Cardinfo returnValue = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strModeId = this.loadSysParam(strCompId, "SP063");
		String strFristModeId = "";// 第一级模板Id
		String strSecondModeId = "";// 第2级模板ID
		String strThirthModeId = "";// 第三级模板Id
		// 先定位模板门店的连锁级别(暂时支持4级连锁)
		int compLvl = this.loadCompLvl(strCompId);
		if (compLvl == 2) {
			strFristModeId = this.loadparentModeId(strModeId);
		} else if (compLvl == 3) {
			strSecondModeId = this.loadparentModeId(strModeId);
			if (!strSecondModeId.equals(""))
				strFristModeId = this.loadparentModeId(strSecondModeId);
		} else if (compLvl == 4) {
			strThirthModeId = this.loadparentModeId(strModeId);
			if (!strThirthModeId.equals(""))
				strSecondModeId = this.loadparentModeId(strThirthModeId);
			if (!strSecondModeId.equals(""))
				strFristModeId = this.loadparentModeId(strSecondModeId);
		}
		String strSql = "select a.cardvesting,a.cardno,a.cardstate,a.salecarddate,a.cutoffdate,a.cardsource,membersex,a.cardtype,cardtypename,membername,membermphone,memberpaperworkno,lowopenamt,lowfillamt,openflag,fillflag,changeflag,sendamtflag,slaeproerate,changerule, memberImageUrl,memberbirthday,password,"
				+ " accountbalance2=sum(case when isnull(accounttype,0)=2 then accountbalance else 0 end ),accountdebts2=sum(case when isnull(accounttype,0)=2 then accountdebts else 0 end ), "
				+ " accountbalance3=sum(case when isnull(accounttype,0)=3 then accountbalance else 0 end ),accountdebts3=sum(case when isnull(accounttype,0)=3 then accountdebts else 0 end ), "
				+ " accountbalance4=sum(case when isnull(accounttype,0)=4 then accountbalance else 0 end ),accountdebts4=sum(case when isnull(accounttype,0)=4 then accountdebts else 0 end ), "
				+ " accountbalance5=sum(case when isnull(accounttype,0)=5 then accountbalance else 0 end ),accountdebts5=sum(case when isnull(accounttype,0)=5 then accountdebts else 0 end ), "
				+ " accountbalance6=sum(case when isnull(accounttype,0)=6 then accountbalance else 0 end ),accountdebts6=sum(case when isnull(accounttype,0)=6 then accountdebts else 0 end ) "
				+ " from cardaccount c with(nolock),cardtypeinfo d with(nolock), cardinfo a with(nolock) left join memberinfo b with(nolock) on  a.cardno=b.memberno where 1=1 and "+ (StringUtils.isNotBlank(strCardNo)?("a.cardno='"+ strCardNo + "' and "):"")
				+ " a.cardno=c.cardno and b.openid='"+ openId + "' and cardstate in(4,5) and a.cardtype=d.cardtypeno and  cardtypemodeid  in ('"+ strModeId+ "','"+ strFristModeId+ "','"+ strSecondModeId+ "','"+ strThirthModeId+ "') "
				+ " group by a.cardvesting,a.cardno,a.cardstate,a.salecarddate,a.cutoffdate,a.cardsource,membersex,a.cardtype,cardtypename,membername,membermphone,memberpaperworkno,lowopenamt,lowfillamt,openflag,fillflag,changeflag,sendamtflag,slaeproerate,changerule, memberImageUrl,memberbirthday,password order by salecarddate";
		ResultSet rs = null;
		try {
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				if (rs.next()) {
					returnValue = new Cardinfo();
					returnValue.setId(new CardinfoId(rs.getString("cardvesting"), rs.getString("cardno")));
					returnValue.setCardstate(CommonTool.FormatInteger(rs.getInt("cardstate")));
					returnValue.setSalecarddate(CommonTool.getDateMask(rs.getString("salecarddate")));
					returnValue.setCutoffdate(CommonTool.getDateMask(rs.getString("cutoffdate")));
					returnValue.setCardtype(CommonTool.FormatString(rs.getString("cardtype")));
					returnValue.setCardsource(CommonTool.FormatInteger(rs.getInt("cardsource")));
					returnValue.setCardtypeName(CommonTool.FormatString(rs.getString("cardtypename")));
					returnValue.setMembername(CommonTool.FormatString(rs.getString("membername")));
					returnValue.setMembersex(CommonTool.FormatInteger(rs.getInt("membersex")));
					returnValue.setMemberphone(CommonTool.FormatString(rs.getString("membermphone")));
					returnValue.setMemberpaperworkno(CommonTool.FormatString(rs.getString("memberpaperworkno")));
					returnValue.setAccount2Amt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountbalance2"))));
					returnValue.setAccount3Amt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountbalance3"))));
					returnValue.setAccount4Amt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountbalance4"))));
					returnValue.setAccount5Amt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountbalance5"))));
					returnValue.setAccount6Amt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountbalance6"))));
					returnValue.setAccount2debtAmt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountdebts2"))));
					returnValue.setAccount3debtAmt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountdebts3"))));
					returnValue.setAccount4debtAmt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountdebts4"))));
					returnValue.setAccount5debtAmt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountdebts5"))));
					returnValue.setAccount6debtAmt(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("accountdebts6"))));
					returnValue.setSlaeproerate(CommonTool.FormatBigDecimal(new BigDecimal(rs.getDouble("slaeproerate"))));
					returnValue.setDSaleLowAmt(CommonTool.FormatBigDecimal(new BigDecimal(CommonTool.FormatDouble(rs.getDouble("lowopenamt")))));
					returnValue.setDFillLowAmt(CommonTool.FormatBigDecimal(new BigDecimal(CommonTool.FormatDouble(rs.getDouble("lowfillamt")))));
					returnValue.setOpenflag(CommonTool.FormatInteger(rs.getInt("openflag")));
					returnValue.setFillflag(CommonTool.FormatInteger(rs.getInt("fillflag")));
					returnValue.setChangeflag(CommonTool.FormatInteger(rs.getInt("changeflag")));
					returnValue.setSendamtflag(CommonTool.FormatInteger(rs.getInt("sendamtflag")));
					returnValue.setChangerule(CommonTool.FormatInteger(rs.getInt("changerule")));
					returnValue.setMemberImageUrl(CommonTool.FormatString(rs.getString("memberImageUrl")));
					returnValue.setMemberbirthday(CommonTool.FormatString(rs.getString("memberbirthday")));
					returnValue.setPassword(CommonTool.FormatString(rs.getString("password")));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return returnValue;
	}
	//修改用户卡密码
	public boolean changePassword(String openId, String cardno, String password){
		AMN_Dao amn_Dao = null;
		String sql = "update memberinfo set password ='"+password+"' where openid='"+openId+"' and cardno='"+ cardno +"'";
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		return amn_Dao.executeSql(sql);
	}
	
	//查询在线订单（家居/到店）
	public List<Map<String, Object>> loadWxOrders(String openid, String catgory){
		AMN_Dao amn_Dao = null;
		List<Map<String, Object>> reuslt = new ArrayList<Map<String, Object>>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String sql = "select *,CONVERT(varchar(100), GETDATE(), 20) orderdate from wx_orders where useropenid='"+ openid +"'"+(StringUtils.isNotBlank(catgory)?(" and catgory="+ catgory):"");
		try {
			rs = amn_Dao.executeQuery(sql);
			while(rs.next()) { 
				Map<String, Object> tmp = new HashMap<String, Object>();
				tmp.put("id", rs.getObject("id"));
				tmp.put("orderno", rs.getObject("orderno"));
				tmp.put("totalAmount", rs.getObject("totalAmount"));
				tmp.put("useropenid", rs.getObject("useropenid"));
				tmp.put("catgory", rs.getObject("catgory"));
				tmp.put("memberno", rs.getObject("memberno"));
				tmp.put("orderstate", rs.getObject("orderstate"));
				tmp.put("ordertime", rs.getObject("orderdate"));
				tmp.put("isExpress", rs.getObject("isExpress"));
				tmp.put("address", rs.getObject("address"));
				tmp.put("username", rs.getObject("username")); 
				tmp.put("phone", rs.getObject("phone"));
				tmp.put("compno", rs.getObject("compno"));
				tmp.put("compname", rs.getObject("compname"));
				tmp.put("companyaddress", rs.getObject("companyaddress"));
				tmp.put("remark", rs.getObject("remark"));
				reuslt.add(tmp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return reuslt;
	}
	@Override
	public List<Account> loadAccount(String openid) {
		AMN_Dao amn_Dao = null;
		List<Account> reuslt = new ArrayList<Account>();
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		ResultSet rs = null;
		String sql = "select isnull(a.memberno,b.memberno) as cardno, isnull(a.bonus,0) as bonus,isnull(b.balance,0) as balance from "
						+"(select memberno,accountbalance as bonus	"
						+"from cardaccount,memberinfo  where  openid = '"+openid+"' and accounttype ='3' "
						+"and cardno = memberno) a "
						+"full join "
						+"(select memberno, accountbalance as balance "
						+"from cardaccount,memberinfo  where  openid = '"+openid+"' and accounttype ='2' "
						+"and cardno = memberno) b "
						+"on a.memberno=b.memberno ";
		try {
			rs = amn_Dao.executeQuery(sql);
			while(rs.next()) { 
				Account aAccount = new Account();
				aAccount.setBalance(rs.getFloat("balance"));
				aAccount.setBonus(rs.getFloat("bonus"));
				aAccount.setCode(rs.getString("cardno"));
				reuslt.add(aAccount);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return reuslt;
	}
	
	// 获取指定openid预约过的设计师
	public Map<String, Object>  loadStaffInfoNo(String staffNo) { 
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao"); 
		String strSql ="select distinct staffinfo.staffno,staffinfo.staffopenid,staffinfo.position, isnull(staffinfo.imageurl,'')  AS staffimageurl, staffinfo.staffname,staffinfo.staffename,companyinfo.compphone, staffinfo.description,staffinfo.compno,companyinfo.compaddress,companyinfo.compname,  companyinfo.xcoordinate,companyinfo.ycoordinate,  5 as starnum,  0 as price,  '' as card_id  from staffinfo inner join companyinfo on Staffinfo.compno = companyinfo.compno where staffinfo.staffno ='"+staffNo+"'";
		ResultSet rs = null;
		Map<String, Object> result = new HashMap<String, Object>();
		try { 
			rs = amn_Dao.executeQuery(strSql);
			if (rs != null) {
				if (rs.next()) {
					
					result.put("staffno", rs.getObject("staffno"));
					result.put("staffopenid", rs.getObject("staffopenid"));
					result.put("staffimageurl", rs.getObject("staffimageurl"));
					result.put("staffname", rs.getObject("staffname"));
					result.put("staffename", rs.getObject("staffename"));
					result.put("description", rs.getObject("description"));
					result.put("compno", rs.getObject("compno"));
					result.put("compaddress", rs.getObject("compaddress"));
					result.put("compname", rs.getObject("compname"));
					result.put("xcoordinate", rs.getObject("xcoordinate"));
					result.put("ycoordinate", rs.getObject("ycoordinate"));
					result.put("starnum", rs.getObject("starnum"));
					result.put("price", rs.getObject("price")==null?0:rs.getObject("price"));
					result.put("cardId", rs.getObject("card_id")==null?"":rs.getObject("card_id"));
					result.put("mobilephone", (String)rs.getObject("compphone"));
					result.put("position", (String)rs.getObject("position"));
				}
			} 
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	@Override
	public boolean saveNointernalcardinfo(java.lang.String cardvesting,
			java.lang.String cardno, int cardtype, float cardfaceamt,
			int carduseflag, int entrytype, int cardstate,
			java.lang.String usedate, java.lang.String useinproject,
			java.lang.String oldvalidate, java.lang.String lastvalidate,
			java.lang.String enabledate, int uespassward,
			java.lang.String cardpassward, int createtype,
			int createcardtype,String openid) {
		boolean result = false;
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "select * from nointernalcardinfo where cardno = '"+cardno+"'";
		ResultSet rs = null;
		rs = amn_Dao.executeQuery(strSql);
		try {
			if (rs.next()){
				result = false;
				rs.close();
			}else{
				strSql = "insert into nointernalcardinfo(cardvesting,cardno,cardtype ,cardfaceamt,carduseflag,entrytype,cardstate,usedate,useinproject,oldvalidate,lastvalidate,enabledate,uespassward,cardpassward,createtype,createcardtype,openid)"
						+ " values('"+cardvesting+"','"+cardno+"',"+cardtype +","+cardfaceamt+","+carduseflag+","+entrytype+","+cardstate+",'"+usedate+"','"+useinproject+"','"+oldvalidate+"','"+lastvalidate+"','"+enabledate+"',"+uespassward+",'"+cardpassward+"','"+createtype+"','"+ createcardtype +"','"+openid+"')";
				result = amn_Dao.executeSql(strSql);
			}
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	@Override
	public boolean saveDnointernalcardinfo(java.lang.String cardvesting,
			java.lang.String cardno, float seqno, java.lang.String ineritemno,
			float entrycount, float usecount, float lastcount, float entryamt,
			float useamt, float lastamt, java.lang.String costbillno,
			java.lang.String entryremark) {
		boolean result = false;
		AMN_Dao amn_Dao = null;
		SpringDBInit sdbinit = SpringDBInit.getInstance();
		amn_Dao = (AMN_Dao) sdbinit.getInstance().getBean("amn_Dao");
		String strSql = "insert into dnointernalcardinfo (cardvesting,cardno,seqno,ineritemno,entrycount,usecount,lastcount	,entryamt,useamt,lastamt,costbillno	,entryremark) "
				+ "values('"+cardvesting+"','"+cardno+"',"+seqno+",'"+ineritemno+"',"+entrycount+","+usecount+","+lastcount+","+entryamt+","+useamt+","+lastamt+",'"+costbillno+"','"+entryremark+"')";
		result = amn_Dao.executeSql(strSql);
		return result;
	}
	
}
